使用 mysql group by 返回计数 0 [英] return count 0 with mysql group by

问题描述

这样的数据库表

============================
= suburb_id   |   value
= 1           |    2
= 1           |    3
= 2           |    4
= 3           |    5

查询是

SELECT COUNT(suburb_id) AS total, suburb_id 
  FROM suburbs 
 where suburb_id IN (1,2,3,4) 
GROUP BY suburb_id

然而，当我运行这个查询时，它不给 COUNT(suburb_id) = 0 当郊区_id = 0因为在郊区表中，没有郊区 ID 4，我希望此查询为郊区 ID = 4 返回 0，例如

however, while I run this query, it doesn't give COUNT(suburb_id) = 0 when suburb_id = 0 because in suburbs table, there is no suburb_id 4, I want this query to return 0 for suburb_id = 4, like

============================
= total       |   suburb_id
= 2           |    1
= 1           |    2
= 1           |    3
= 0           |    4

推荐答案

GROUP BY 需要处理行，因此如果某个类别没有行，您将无法获得计数.将 where 子句视为在将源行组合在一起之前对其进行限制.where 子句未提供分组依据的类别列表.

A GROUP BY needs rows to work with, so if you have no rows for a certain category, you are not going to get the count. Think of the where clause as limiting down the source rows before they are grouped together. The where clause is not providing a list of categories to group by.

您可以做的是编写一个查询来选择类别(郊区)，然后在子查询中进行计数.(我不确定 MySQL 对此的支持是怎样的)

What you could do is write a query to select the categories (suburbs) then do the count in a subquery. (I'm not sure what MySQL's support for this is like)

类似于:

SELECT 
  s.suburb_id,
  (select count(*) from suburb_data d where d.suburb_id = s.suburb_id) as total
FROM
  suburb_table s
WHERE
  s.suburb_id in (1,2,3,4)

(MSSQL，抱歉)

这篇关于使用 mysql group by 返回计数 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！