使用COUNT、GROUP BY与FIELD统计时缺失值返回0的实现咨询

解决方案 #

核心原因：原语句无法返回计数为0的status，是因为WHERE过滤后结果集中不存在对应status的行，GROUP BY仅会聚合已有行，IFNULL没有作用对象。

可以通过构造包含所有待统计status的固定值列表，和业务表做左连接实现需求，兼容主流关系型数据库的写法如下：

SELECT 
  COUNT(t.status) AS `COUNT(*)`,
  v.status
FROM (
  -- 构造需要统计的所有status值列表
  SELECT 'value1' AS status
  UNION ALL SELECT 'value2'
  UNION ALL SELECT 'value3'
  UNION ALL SELECT 'value4'
  UNION ALL SELECT 'value5'
) v
LEFT JOIN `table` t 
  ON v.status = t.status 
  AND t.some_field = 12345 -- 业务过滤条件必须放在ON子句中，不能放到WHERE里
GROUP BY v.status
ORDER BY FIELD(v.status, 'value1', 'value2', 'value3', 'value4', 'value5');

逻辑说明

• 内层派生表v会固定返回你需要统计的5个status行，无论业务表有没有对应记录，这5行都会存在
• 左连接匹配不到业务表记录时，t.status会为NULL，COUNT(t.status)仅统计非NULL值，无匹配的status自然返回0
• 业务过滤条件如果放到WHERE子句中，会过滤掉左连接生成的t.status为NULL的行，回到原有问题，因此必须放到LEFT JOIN的ON子句中

内容的提问来源于stack exchange，提问作者Cesar Bielich