sql - 按公共(public)字段组合两个查询组的输出

Question

我想合并这两个查询的结果:

SELECT odate, 
       Count(odate), 
       Sum(dur) 
FROM   table1 t1 
GROUP  BY odate 
ORDER  BY odate; 

SELECT cdate, 
       Count(cdate), 
       Sum(dur) 
FROM   table2 t2 
GROUP  BY cdate 
ORDER  BY cdate; 

结果是这样的:

odate,t1.count(odate),t2.sum(dur),t2.count(cdate),t2.sum(dur) order by odate

怎么做？

运行这个时出现错误:

select odate,count(odate),sum(dur) 
from table1 t1 
group by odate 
order by odate 
union 
select cdate,count(cdate),sum(dur) 
from table2 t2 
group by cdate 
order by cdate;

Answer 1

查看您想要的结果，您需要一个 JOIN 而不是 UNION。你可以这样做

select coalesce(odate, cdate) odate, count1, sum1, count2, sum2
  from
(
  select odate, count(odate) count1, sum(dur) sum1
    from table1
   group by odate
) t1 full join
(
  select cdate, count(cdate) count2, sum(dur) sum2
    from table2
   group by cdate
) t2
    on t1.odate = t2.cdate
 order by odate;

示例输出:

|                          ODATE | COUNT1 |   SUM1 | COUNT2 |   SUM2 ||--------------------------------|--------|--------|--------|--------|| January, 01 2013 00:00:00+0000 |      2 |     30 |      2 |     30 || January, 02 2013 00:00:00+0000 |      1 |     30 | (null) | (null) || January, 03 2013 00:00:00+0000 | (null) | (null) |      1 |     30 |

这是 SQLFiddle 演示