我有这个带有数据的表结构:
INSERT INTO `test` (`id`, `email`, `id_user_ref`, `name`) VALUES
(1, '[email protected] /cdn-cgi/l/email-protection', NULL, 'Mike'),
(2, '[email protected] /cdn-cgi/l/email-protection', '1', 'Jhonny'),
(3, '[email protected] /cdn-cgi/l/email-protection', '1', 'Michael'),
(4, '[email protected] /cdn-cgi/l/email-protection', '2', 'Jorhe'),
(5, '[email protected] /cdn-cgi/l/email-protection', '3', 'Mia');
我需要使用此查询计算所有用户的 id_user_ref :
SELECT id, COUNT(name) AS refNr FROM test GROUP BY id_user_ref
HAVING id_user_ref IS NOT NULL;
这可行,但问题是即使计数结果为 0,我也需要显示所有结果。
我尝试了对同一张表的多次左连接,但没有成功。
输出应该是:
id refNr
1 2
2 1
3 1
4 0
5 0