mysql 带有 group by 和表别名的完全外连接

Question

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);

然后，我为了执行正确连接(以完成外部连接)而尝试的所有操作都会给我带来语法错误。问题似乎出在表别名上。

如果有人可以帮助我完成外部连接，我将不胜感激。 :)

感谢您阅读本文。

Answer 1

使用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;