mysql - 将两个表的总和与日期相结合的查询

Question

我有两个如下表

挑战表

Challenger         chdate
----------------------
abc                11-02-2012
aaa                12-02-2012
ccc                12-02-2012
bbb                13-02-2012
ddd                14-02-2012

init_table

Initiateid         indate
----------------------
a1                11-02-2012
a2                11-02-2012
a3                12-02-2012
a4                13-02-2012
a5                13-02-2012

我需要这样的结果

challengecount  initcount   curdate
-----------------------------------
1                2          11-02-2012
2                1          12-02-2012
1                2          13-02-2012
1                0          14-02-2012

我尝试了这样的查询

SELECT COUNT(*) challengecount, chdate caldate FROM challenge_table
UNION ALL 
SELECT COUNT(*) Initiatecount, indate caldate FROM init_table

但这对我不起作用。

Answer 1

由于 MySQL 不支持 FULL OUTER JOIN，因此您需要使用 UNION 将 2 个表组合起来。

然后按日期分组并计算 2 个表的值

select caldate, count(distinct Challenger), count(distinct Initiateid)
from
(
    SELECT Challenger, null as Initiateid, chdate as caldate FROM challenge_table
    UNION ALL 
    SELECT null, Initiateid, indate FROM init_table
) tmp
group by caldate