返回计数0与mysql组 [英] 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当suburb_id = 0
因为在郊区表中，没有suburb_id 4，我希望此查询返回0为suburb_id = 4，如

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，apologies）

(MSSQL, apologies)

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