MySQL 计数在同一个表中，包括零计数值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15758201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL count in same table including zero count values
提问by Dsp Marian
I have this table structure with data:
我有这个包含数据的表结构：
INSERT INTO `test` (`id`, `email`, `id_user_ref`, `name`) VALUES
(1, '[email protected]', NULL, 'Mike'),
(2, '[email protected]', '1', 'Jhonny'),
(3, '[email protected]', '1', 'Michael'),
(4, '[email protected]', '2', 'Jorhe'),
(5, '[email protected]', '3', 'Mia');
I need to count the id_user_ref for all users with this query:
我需要使用此查询计算所有用户的 id_user_ref ：
SELECT id, COUNT(name) AS refNr FROM test GROUP BY id_user_ref
HAVING id_user_ref IS NOT NULL;
This works but the problem is that i need to display all results even if the count result is 0.
这有效，但问题是即使计数结果为 0，我也需要显示所有结果。
I tried several left joins with the same table but without any success.
我在同一张桌子上尝试了几个左连接，但没有成功。
The output should be:
输出应该是：
id refNr
1 2
2 1
3 1
4 0
5 0
回答by Mahmoud Gamal
Try this:
尝试这个：
SELECT
t1.id,
IFNULL(COUNT(t2.name), 0) AS refNr
FROM test AS t1
LEFT JOIN test AS t2 ON t1.id = t2.id_user_ref
GROUP BY t1.id;
This will give you:
这会给你：
| ID | REFNR |
--------------
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
