I have this table structure with data:
我有这个表结构与数据:
INSERT INTO `test` (`id`, `email`, `id_user_ref`, `name`) VALUES
(1, 'email@gmail.com', NULL, 'Mike'),
(2, 'email2@gmail.com', '1', 'Jhonny'),
(3, 'email3@gmail.com', '1', 'Michael'),
(4, 'email4@gmail.com', '2', 'Jorhe'),
(5, 'email5@gmail.com', '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
2 个解决方案
#1
13
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;
SQL Fiddle DEmo
This will give you:
这会给你:
| ID | REFNR |
--------------
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
#2
0
Can you try this ?
你能试试吗?
SELECT a.id,
CASE WHEN b.refNr IS NULL THEN 0
ELSE b.refNr END FROM test a LEFT JOIN
( SELECT id_user_ref, COUNT(name) AS refNr
FROM test
WHERE id_user_ref IS NOT NULL
GROUP BY id_user_ref) b
ON a.id = b.id_user_ref
#1
13
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;
SQL Fiddle DEmo
This will give you:
这会给你:
| ID | REFNR |
--------------
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
#2
0
Can you try this ?
你能试试吗?
SELECT a.id,
CASE WHEN b.refNr IS NULL THEN 0
ELSE b.refNr END FROM test a LEFT JOIN
( SELECT id_user_ref, COUNT(name) AS refNr
FROM test
WHERE id_user_ref IS NOT NULL
GROUP BY id_user_ref) b
ON a.id = b.id_user_ref