按学生分组实现SQL条件逻辑聚合：成绩表转换需求求助

原始表数据 #

TABLE1
STUDENT SCORE   SCOREVAL
1       PASS    10
1       MOD1    4
1       MOD2    5
2       MOD1    4
3       MOD2    2
3       MOD2    1
3       MOD1    9
3       ER      1
4       MOD2    10
4       ER      6

期望转换结果 #

STUDENT SCORE   SCOREVAL
1       PASS    10
2       MOD1    4
3       PASS    11
4       MOD2    10

转换规则 #

针对每个学生的SCORE字段： #

• 若存在任意PASS值，聚合后SCORE为PASS
• 若同时包含MOD1和MOD2值，聚合后SCORE为PASS
• 若仅包含MOD1值，聚合后SCORE为MOD1
• 若仅包含MOD2值，聚合后SCORE为MOD2

针对每个学生的SCOREVAL字段： #

• 若存在PASS值，取PASS对应的最大SCOREVAL
• 若同时包含MOD1和MOD2，取MOD1的最大SCOREVAL与MOD2的最大SCOREVAL之和
• 若仅包含MOD1，取MOD1对应的最大SCOREVAL
• 若仅包含MOD2，取MOD2对应的最大SCOREVAL

你尝试的错误SQL #

SELECT * 
FROM TABLE1 
GROUP BY STUDENT when SCORE = 'PASS' then 'PASS'
when SCORE = 'MOD1' and SCORE = 'MOD2' then 'PASS'
when SCORE = 'MOD1' then 'MOD1'
when SCORE = 'MOD2' then 'MOD2'
else EXEMPT
end

正确实现方案 #

这里用CTE先聚合每个学生的关键标记和分数值，再通过CASE语句匹配规则，逻辑清晰且符合需求：

WITH student_scores AS (
    SELECT 
        STUDENT,
        -- 标记是否存在对应类型的分数
        MAX(CASE WHEN SCORE = 'PASS' THEN 1 ELSE 0 END) AS has_pass,
        MAX(CASE WHEN SCORE = 'MOD1' THEN 1 ELSE 0 END) AS has_mod1,
        MAX(CASE WHEN SCORE = 'MOD2' THEN 1 ELSE 0 END) AS has_mod2,
        -- 计算各类型的最大分数值
        MAX(CASE WHEN SCORE = 'PASS' THEN SCOREVAL ELSE 0 END) AS max_pass_val,
        MAX(CASE WHEN SCORE = 'MOD1' THEN SCOREVAL ELSE 0 END) AS max_mod1_val,
        MAX(CASE WHEN SCORE = 'MOD2' THEN SCOREVAL ELSE 0 END) AS max_mod2_val
    FROM TABLE1
    GROUP BY STUDENT
)
SELECT 
    STUDENT,
    CASE
        WHEN has_pass = 1 THEN 'PASS'
        WHEN has_mod1 = 1 AND has_mod2 = 1 THEN 'PASS'
        WHEN has_mod1 = 1 THEN 'MOD1'
        WHEN has_mod2 = 1 THEN 'MOD2'
        ELSE 'EXEMPT' -- 可根据实际需求调整其他情况的处理
    END AS SCORE,
    CASE
        WHEN has_pass = 1 THEN max_pass_val
        WHEN has_mod1 = 1 AND has_mod2 = 1 THEN max_mod1_val + max_mod2_val
        WHEN has_mod1 = 1 THEN max_mod1_val
        WHEN has_mod2 = 1 THEN max_mod2_val
        ELSE 0 -- 可根据实际需求调整其他情况的处理
    END AS SCOREVAL
FROM student_scores;

方案说明： #

1. 先通过CTEstudent_scores对每个学生进行聚合，判断该学生是否有PASS、MOD1、MOD2类型的分数，同时计算出各类型分数的最大值
2. 外层查询通过CASE语句按照规则依次判断，生成最终的SCORE和SCOREVAL字段
3. 对于ER这类无关类型的分数，由于规则未提及，聚合时会自动忽略，不影响结果

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