SQL分组查询如何展示缺失的Code类别及对应占比

要实现展示所有Code类别（Green、Blue、Red、Yellow），同时为无数据的类别填充0%占比，核心是先生成所有可能的(State, School, Code)组合，再关联原表的统计数据，具体实现如下：

实现思路 #

1. 生成所有唯一的Code值集合：枚举已知的4种Code，或从维度表获取（如果有）。
2. 生成所有唯一的(State, School)组合：从原表中去重提取。
3. 交叉连接上述两个集合，得到全量的组合。
4. 左连接原表的分组统计结果，对无匹配的项用0填充占比。

具体SQL代码 #

WITH code_counts AS (
    -- 先统计每个(State, School, Code)的记录数
    SELECT
        State,
        School,
        Code,
        COUNT(*) AS cnt
    FROM grade_code
    GROUP BY State, School, Code
),
all_codes AS (
    -- 枚举所有可能的Code值
    SELECT 'Green' AS Code UNION ALL
    SELECT 'Blue' UNION ALL
    SELECT 'Red' UNION ALL
    SELECT 'Yellow'
),
all_schools AS (
    -- 获取所有唯一的州+学校组合
    SELECT DISTINCT State, School
    FROM grade_code
)
-- 生成全量组合并计算占比
SELECT
    s.State,
    s.School,
    c.Code,
    -- 计算占比，无数据则显示0%
    CONCAT(ROUND(COALESCE(cc.cnt * 100.0 / (SELECT COUNT(*) FROM grade_code), 0), 0), '%') AS '%'
FROM all_schools s
CROSS JOIN all_codes c
LEFT JOIN code_counts cc 
    ON s.State = cc.State 
    AND s.School = cc.School 
    AND c.Code = cc.Code
ORDER BY s.State, s.School, c.Code;

说明 #

• 如果存在专门的Code维度表（比如code_dim），可以将all_codes部分替换为SELECT Code FROM code_dim，避免硬编码。
• 不同数据库的函数语法可能略有差异（比如字符串拼接、取整函数），但核心逻辑通用。
• COALESCE函数用于将左连接后NULL的计数转为0，确保占比计算正确。

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