基于TABLE1生成TABLE2的SQL查询需求及错误修正咨询

原SQL的问题点 #

• CASE表达式语法错误：正确语法是CASE WHEN 条件 THEN 结果 ELSE 其他结果 END，你误用逗号代替了THEN
• 错误使用GROUP BY：直接按STUDENT分组会丢失每个ENGLISH选课的明细记录，不符合需求
• 未做必要筛选：没有限制仅保留修过ENGLISH的学生，也没过滤出ENGLISH的选课记录

正确的SQL语句 #

SELECT 
    t1.STUDENT,
    t1.DATE,
    agg.LAST_ENGLISH,
    agg.ANY_HISTORY,
    agg.ANY_OTHER
FROM TABLE1 t1
INNER JOIN (
    -- 计算每个符合条件学生的聚合指标
    SELECT 
        STUDENT,
        -- 获取该学生所有ENGLISH选课的最晚日期
        MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN DATE END) AS LAST_ENGLISH,
        -- 修过HISTORY返回1，否则返回0
        MAX(CASE WHEN SUBJECT = 'HISTORY' THEN 1 ELSE 0 END) AS ANY_HISTORY,
        -- 修过ART或SCIENCE返回1，否则返回0
        MAX(CASE WHEN SUBJECT IN ('ART', 'SCIENCE') THEN 1 ELSE 0 END) AS ANY_OTHER
    FROM TABLE1
    GROUP BY STUDENT
    -- 仅保留至少修过一次ENGLISH的学生
    HAVING MAX(CASE WHEN SUBJECT = 'ENGLISH' THEN 1 END) = 1
) agg ON t1.STUDENT = agg.STUDENT
-- 仅保留该学生的ENGLISH选课记录
WHERE t1.SUBJECT = 'ENGLISH'

逻辑说明 #

1. 子查询agg按学生分组，计算三个全局指标，同时通过HAVING筛选出修过ENGLISH的学生
2. 主查询关联原表和子查询，筛选出原表中所有ENGLISH选课记录，同时带上该学生的聚合指标
3. 用MAX()配合CASE表达式，确保只要学生有对应科目选课，就能得到正确的标记值（1）

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