使用LEFT JOIN和GROUP BY的COUNT(*)在MySQL中包括NULL [英] COUNT(*) with LEFT JOIN and GROUP BY to include NULL in MySQL

问题描述

我正在尝试从包含外部表中出现次数的表中获取结果.该表可以有0个或更多的外观.

I'm trying to get the results from a table including the appearance count in a foreign table. This table can have 0 or more appearances.

如以下示例所示:

表格:颜色

+------+---------+
|  id  |   name  |
+------+---------+
|  1   |   red   |
|  2   |   blue  |
|  3   |  yellow |
|  4   |  green  |
+------+---------+

表格:水果

+--------+----------+
|  name  | color_id |
+--------+----------+
| apple  |    1     |
| banana |    3     |
| grape  |    4     |
| lemon  |    3     |
+--------+----------+

所以我需要列出水果表中的每种颜色和出现的位置，并返回如下内容:

So I need to list every color and the occurrence in the fruit table, returning something like this:

1, red, 1
2, blue, 0
3, yellow, 2
4, green, 1

我正在尝试以下查询:

SELECT `c`.`id`, `c`.`name`, COUNT(1)
FROM color `c`
LEFT JOIN fruit `f`
ON `c`.`id` = `f`.`color_id`
GROUP BY `c`.`id`

此查询返回的计数为"<蓝色> 1"，而不是"<蓝色> 0".因为水果表中没有出现蓝色"

This query is returning a count of 1 for "blue" instead of 0. beacuse the color "blue" doesn't appear in the fruit table

推荐答案

这有效:

SELECT c.id, COUNT(f.name)
FROM color c
LEFT JOIN fruit f ON c.id = f.color_id
GROUP BY c.id

您必须计算一个水果字段，以便可以返回NULL，该值变为零.

You have to count a field of fruit, so that NULL can be returned, which becomes a zero.

这篇关于使用LEFT JOIN和GROUP BY的COUNT(*)在MySQL中包括NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！