SQL多列分组设置过滤条件时如何包含计数为0的记录

核心逻辑 #

你遇到的问题本质是没有固定要输出的维度基准行，同时过滤条件的位置错误导致空值行被过滤。正确的做法是先生成所有需要展示的category和impact的全组合作为基准表，再左连接经过过滤后的业务数据做统计，即可保留计数为0的行。

完整SQL示例 #

SELECT 
  base.category,
  base.impact,
  COUNT(t.number) AS count
FROM (
  -- 生成需要展示的全量<分类,影响等级>维度组合，此处固定查Animal分类的所有影响等级
  SELECT DISTINCT 'Animal' AS category, impact
  FROM tbl
) base
LEFT JOIN (
  -- 所有过滤条件都放在业务数据子查询中，避免过滤掉基准行
  SELECT *
  FROM tbl
  WHERE category = 'Animal'
    AND company = 'A'
    -- 可自行追加date过滤规则，例如：AND date >= '1999-01-01'
) t ON base.category = t.category AND base.impact = t.impact
GROUP BY base.category, base.impact
ORDER BY base.impact;

逻辑说明 #

1. 基准表base固定了最终要输出的所有行，不管有没有匹配的业务数据都会保留，不会出现category为NULL的情况
2. 所有过滤条件都放在业务数据的子查询中预处理，不会影响基准表的行，没有匹配业务数据的行自然计数为0
3. 如果需要查询全部分类的所有组合，将基准表替换为如下写法即可：

SELECT DISTINCT a.category, b.impact
FROM tbl a CROSS JOIN tbl b

结果验证 #

以上SQL在你的示例数据中执行后，将直接得到你期望的输出：

category | impact   | count
---------+----------+-------
Animal   | Critical |   1
Animal   | Medium   |   0
Animal   | Minor    |   0

内容的提问来源于stack exchange，提问作者Danny Smith