MySQL在同一表中计数，包括零计数值 [英] MySQL count in same table including zero count values

问题描述

我有这个带有数据的表结构:

I have this table structure with data:

INSERT INTO `test` (`id`, `email`, `id_user_ref`, `name`) VALUES
(1, '[e-mail]', NULL, 'Mike'),
(2, '[e-mail]', '1', 'Jhonny'),
(3, '[e-mail]', '1', 'Michael'),
(4, '[e-mail]', '2', 'Jorhe'),
(5, '[e-mail]', '3', 'Mia');

我需要使用此查询为所有用户计算id_user_ref:

I need to count the id_user_ref for all users with this query:

SELECT id, COUNT(name) AS refNr FROM test GROUP BY id_user_ref
HAVING id_user_ref IS NOT NULL;

这有效，但问题是即使计数结果为0，我也需要显示所有结果.

This works but the problem is that i need to display all results even if the count result is 0.

我尝试了使用同一张桌子的几个左联接，但没有成功.

I tried several left joins with the same table but without any success.

输出应为:

id  refNr
1    2
2    1
3    1
4    0
5    0

推荐答案

尝试一下:

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;

SQL Fiddle DEmo

这将为您提供:

| ID | REFNR |
--------------
|  1 |     2 |
|  2 |     1 |
|  3 |     1 |
|  4 |     0 |
|  5 |     0 |

这篇关于MySQL在同一表中计数，包括零计数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！