DB2 SQL 使用分组依据读取 2 个表

Question

我需要读取 2 个表选择唯一日期，并按日期计算两个表中的条目数。

Example:
table 1                table 2
date                   date
----------             --------
2018-03-20             2018-03-15
2018-03-20             2018-03-20
2018-03-25             

最终结果集应为：

date         sum from table 1  sum from table 2    total count
2018-03-15    0                     1                      1
2018-03-20    2                     1                      3
2018-03-25    1                     0                      1

有人可以帮忙吗，我应该如何编写最终结果的代码，如上所示

Answer 1

我建议使用 union all 和 group by 来执行此操作：

select date, sum(in_1) as sum_1, sum(in_2) as cnt_2,
       sum(in_1) + sum(in_2) as total_cnt
from ((select date, 1 as in_1, 0 as in_2 from table1) union all
      (select date, 0, 1 from table2)
     ) t
group by date
order by date;

Answer 2

从表1和表2中全部合并，然后进行分组。汇总每个表源的行计数。

select date,
   sum(case when tbl=1 then 1 else 0 end) as sum_tbl_1,
   sum(case when tbl=2 then 1 else 0 end) as sum_tbl_2,
   sum(1) as total_count
from (
  select date, 1 as tbl
  from myTable1
    union all
  select date, 2 as tbl
from myTable2) t
group by t.date
order by t.date