mysql 连接2个表并计算每个表中的所有行

Question

我有表合约 c (id, exp_date) 和表成员 m (id, cid)。我需要统计加入 m.cid = c.id 上的 2 个表的所有合约和所有成员。

我尝试了这个，但显然不对，因为它从两个表返回相同的计数结果

SELECT count(m.id) as totmembers , count(c.id) as totcontracts
from members m
join contracts c on m.cid = c.id
where DATEDIFF(c.im_exp, CURDATE()) > 0

结果应该类似于 5000 个合约，12.000 个成员，但我为 totmembers 和 totcontracts 都获得了 12.000 个。

Answer 1

试试这个:

SELECT count(m.id) as totmembers , count(distinct c.id) as totcontracts
from members m
join contracts c on m.cid = c.id
where DATEDIFF(c.im_exp, CURDATE()) > 0