如何用SQL按年龄组、教育水平统计索赔总金额并生成指定格式结果

你需要补全原SQL中缺失的字段，并通过窗口函数处理年龄组的重复显示问题，以下是能生成指定格式结果的完整SQL：

SELECT 
    CASE 
        WHEN rn = 1 THEN age_group 
        ELSE '' 
    END AS 年龄组,
    EDUCATION AS 教育水平,
    COALESCE(SUM(CLM_AMT), 0) AS 索赔金额
FROM (
    SELECT 
        CASE
            WHEN AGE <20 THEN '<20'
            WHEN AGE BETWEEN 20 AND 29 THEN '20-30'
            WHEN AGE BETWEEN 30 AND 39 THEN '30-40'
            WHEN AGE BETWEEN 40 AND 49 THEN '40-50'
            WHEN AGE BETWEEN 50 AND 60 THEN '50-60'
            WHEN AGE > 60 THEN '>60'
        END AS age_group,
        EDUCATION,
        CLM_AMT,
        ROW_NUMBER() OVER (PARTITION BY 
            CASE
                WHEN AGE <20 THEN '<20'
                WHEN AGE BETWEEN 20 AND 29 THEN '20-30'
                WHEN AGE BETWEEN 30 AND 39 THEN '30-40'
                WHEN AGE BETWEEN 40 AND 49 THEN '40-50'
                WHEN AGE BETWEEN 50 AND 60 THEN '50-60'
                WHEN AGE > 60 THEN '>60'
            END 
            ORDER BY EDUCATION) AS rn
    FROM Car.Claim
) t
GROUP BY age_group, EDUCATION, rn
ORDER BY 
    CASE age_group 
        WHEN '<20' THEN 1
        WHEN '20-30' THEN 2
        WHEN '30-40' THEN 3
        WHEN '40-50' THEN 4
        WHEN '50-60' THEN 5
        WHEN '>60' THEN 6
    END,
    rn;

如果你的数据库支持WITH子句，也可以把年龄组逻辑抽离，让代码更简洁：

WITH age_groups AS (
    SELECT 
        CASE
            WHEN AGE <20 THEN '<20'
            WHEN AGE BETWEEN 20 AND 29 THEN '20-30'
            WHEN AGE BETWEEN 30 AND 39 THEN '30-40'
            WHEN AGE BETWEEN 40 AND 49 THEN '40-50'
            WHEN AGE BETWEEN 50 AND 60 THEN '50-60'
            WHEN AGE > 60 THEN '>60'
        END AS age_group,
        EDUCATION,
        CLM_AMT
    FROM Car.Claim
)
SELECT 
    CASE WHEN rn = 1 THEN age_group ELSE '' END AS 年龄组,
    EDUCATION AS 教育水平,
    COALESCE(SUM(CLM_AMT), 0) AS 索赔金额
FROM (
    SELECT 
        age_group,
        EDUCATION,
        CLM_AMT,
        ROW_NUMBER() OVER (PARTITION BY age_group ORDER BY EDUCATION) AS rn
    FROM age_groups
) t
GROUP BY age_group, EDUCATION, rn
ORDER BY 
    CASE age_group 
        WHEN '<20' THEN 1
        WHEN '20-30' THEN 2
        WHEN '30-40' THEN 3
        WHEN '40-50' THEN 4
        WHEN '50-60' THEN 5
        WHEN '>60' THEN 6
    END,
    rn;

关键说明 #

• 补全核心字段：子查询必须包含EDUCATION和CLM_AMT，否则无法按教育水平分组、统计索赔总金额。
• 窗口函数控制显示：通过ROW_NUMBER()给每个年龄组内的教育水平排序编号，仅在组内第一条记录显示年龄组，其余显示空字符串，实现你要的格式效果。
• 固定排序逻辑：用CASE语句指定年龄组的排序优先级，避免字符串默认排序导致顺序混乱（比如>60会排在<20前面）。
• 空值自定义处理：COALESCE将无索赔记录的空值转为0，若希望显示空值而非0，直接去掉该函数写SUM(CLM_AMT) AS 索赔金额即可。

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