SQL使用CASE语句分组统计未返回全部分类，如何补全计数为0的项？

核心逻辑是先构造完整的预设分类集合，通过左关联匹配统计结果，无匹配数据的分类计数自动补0，兼容绝大多数SQL语法。

写法1：CTE 写法（适配支持公共表表达式的数据库） #

WITH all_categories AS (
    -- 枚举所有预设分类，字符串需和CASE返回值完全一致
    SELECT 'Expiration < 30 days' AS Expiration_Version
    UNION ALL
    SELECT 'Expiration between 31 and 60 days' AS Expiration_Version
    UNION ALL
    SELECT 'Expiration between 61 and 90 days' AS Expiration_Version
    UNION ALL
    SELECT 'Expiration > 90 days' AS Expiration_Version
),
stat_result AS (
    -- 原有分组统计逻辑
    SELECT 
        CASE 
            WHEN day <= 30 THEN 'Expiration < 30 days'
            WHEN day BETWEEN 31 AND 60 THEN 'Expiration between 31 and 60 days'
            WHEN day BETWEEN 61 AND 90 THEN 'Expiration between 61 and 90 days'
            WHEN day > 90 THEN 'Expiration > 90 days' 
        END Expiration_Version,
        COUNT(*) AS cnt
    FROM tb1
    GROUP BY Expiration_Version
)
SELECT 
    a.Expiration_Version AS 'Expiration Version',
    COALESCE(s.cnt, 0) AS Count
FROM all_categories a
LEFT JOIN stat_result s ON a.Expiration_Version = s.Expiration_Version;

写法2：子查询写法（兼容所有数据库，包括不支持CTE的低版本数据库） #

SELECT 
    a.Expiration_Version AS 'Expiration Version',
    COALESCE(s.cnt, 0) AS Count
FROM (
    SELECT 'Expiration < 30 days' AS Expiration_Version
    UNION ALL
    SELECT 'Expiration between 31 and 60 days' AS Expiration_Version
    UNION ALL
    SELECT 'Expiration between 61 and 90 days' AS Expiration_Version
    UNION ALL
    SELECT 'Expiration > 90 days' AS Expiration_Version
) a
LEFT JOIN (
    SELECT 
        CASE 
            WHEN day <= 30 THEN 'Expiration < 30 days'
            WHEN day BETWEEN 31 AND 60 THEN 'Expiration between 31 and 60 days'
            WHEN day BETWEEN 61 AND 90 THEN 'Expiration between 61 and 90 days'
            WHEN day > 90 THEN 'Expiration > 90 days' 
        END Expiration_Version,
        COUNT(*) AS cnt
    FROM tb1
    GROUP BY Expiration_Version
) s ON a.Expiration_Version = s.Expiration_Version;

说明 #

COALESCE 函数作用是将左关联产生的空值替换为0，如果你使用的数据库支持IFNULL、NVL函数，替换为对应函数效果完全一致。

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