Table 1
DATE dt
int amt
Table 2
DATE dt
int amt
日期列在每个表中不是唯一的。我希望能够看到存在于任何一个表中的任何日期的每个金额的总和,如下所示:
date t1.amt t2.amt
...
这里是我所得到的:
select coalesce(t1.dt, t2.dt), t1.amount, t2.amount from
(Select dt, sum(amt) from t1 group by dt) As t1
left join
(SELECT dt, sum(amt) from t2 group by dt) as t2
on (t1.dt = t2.dt);
然后,我为了执行右连接(完成外连接)而尝试的所有操作都给了我语法错误。问题似乎与表别名有关。
如果有人能帮我完成外连接,我将不胜感激。:)
感谢您的阅读。
使用union all
和group by
:
select dt, sum(amt)
from ((select dt, amt from t1) union all
(select dt, amt from t2)
) tt
group by dt;
如果你想让它们分开列,使用两列:
select dt, sum(amt1), sum(amt2)
from ((select dt, amt as amt1, NULL as amt2 from t1) union all
(select dt, NULL, amt from t2)
) tt
group by dt;