多列GROUP BY时如何用COUNT(*)返回0值计数

要让查询返回包含计数为0的目标组合，核心是先构建所有需要的(year, code, type)组合，再通过左连接原表进行统计，具体方案如下：

方法一：手动指定目标组合 #

如果明确知道所有需要的组合，可以直接用VALUES子句生成全组合数据集，再左连接原表：

WITH all_combinations AS (
    SELECT year, code, type
    FROM (VALUES 
        (2020, 'A', 'Z'),
        (2020, 'B', 'Y'),
        (2020, 'C', 'X')
    ) AS t(year, code, type)
)
SELECT 
    ac.year, 
    ac.code, 
    ac.type, 
    COUNT(st2.year) AS count  -- 用原表列而非COUNT(*)，NULL会被忽略
FROM all_combinations ac
LEFT JOIN #senatetest2 st2 
    ON ac.year = st2.year 
    AND ac.code = st2.code 
    AND ac.type = st2.type
GROUP BY ac.year, ac.code, ac.type
ORDER BY ac.year, ac.code, ac.type;

方法二：用维度表生成全组合（更灵活） #

如果有现成的维度表（比如年份表、代码表、类型表），或者可以从原表提取 distinct 值，用交叉连接生成所有可能的组合：

WITH all_combinations AS (
    SELECT 
        y.year, 
        c.code, 
        t.type
    FROM 
        -- 提取原表中所有年份，或用专门的年份维度表
        (SELECT DISTINCT year FROM #senatetest2) y
        -- 所有需要的code值
        CROSS JOIN (SELECT 'A' AS code UNION ALL SELECT 'B' UNION ALL SELECT 'C') c
        -- 所有需要的type值
        CROSS JOIN (SELECT 'Z' AS type UNION ALL SELECT 'Y' UNION ALL SELECT 'X') t
)
SELECT 
    ac.year, 
    ac.code, 
    ac.type, 
    COUNT(st2.year) AS count
FROM all_combinations ac
LEFT JOIN #senatetest2 st2 
    ON ac.year = st2.year 
    AND ac.code = st2.code 
    AND ac.type = st2.type
GROUP BY ac.year, ac.code, ac.type
ORDER BY ac.year, ac.code, ac.type;

关键说明 #

• 原查询只返回计数>0的记录，因为GROUP BY仅基于原表中存在的行分组，不存在的组合不会被生成。
• 必须使用COUNT(原表列)而非COUNT(*)：COUNT(*)会统计左连接后的所有行（包括NULL行），返回1而非0；COUNT(原表列)会忽略NULL值，没有匹配数据时返回0。

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