SQL新手练习：如何结合双表按指定条件转换YEAR列值

嗨，我来帮你搞定这个SQL问题！你之前的尝试只用到了TABLE_A的当前选课数据，但规则2和3需要依赖TABLE_B里的已完成课程记录，所以我们得把两张表的数据结合起来分析。下面是完整的可运行SQL，以及详细的步骤解释：

完整SQL代码 #

WITH student_completed_courses AS (
    SELECT 
        STUDENT_ID,
        -- 标记是否在过往学期完成了1009（非2024学期的都算已完成）
        MAX(CASE WHEN CLASS = '1009' AND TERM_YEAR < 2024 THEN 1 ELSE 0 END) AS completed_1009,
        MAX(CASE WHEN CLASS = '1029' AND TERM_YEAR < 2024 THEN 1 ELSE 0 END) AS completed_1029,
        -- 标记是否在过往学期完成了2011
        MAX(CASE WHEN CLASS = '2011' AND TERM_YEAR < 2024 THEN 1 ELSE 0 END) AS completed_2011,
        MAX(CASE WHEN CLASS = '2021' AND TERM_YEAR < 2024 THEN 1 ELSE 0 END) AS completed_2021
    FROM TABLE_B
    GROUP BY STUDENT_ID
),
current_enrollments AS (
    SELECT 
        ID AS STUDENT_ID,
        YEAR AS TERM_YEAR,
        LAST_NAME,
        FIRST_NAME,
        DEGREE,
        CREDIT,
        -- 标记当前学期是否注册了1009
        MAX(CASE WHEN COURSE = '1009' THEN 1 ELSE 0 END) AS enrolled_1009,
        MAX(CASE WHEN COURSE = '1029' THEN 1 ELSE 0 END) AS enrolled_1029,
        -- 标记当前学期是否注册了2011
        MAX(CASE WHEN COURSE = '2011' THEN 1 ELSE 0 END) AS enrolled_2011,
        MAX(CASE WHEN COURSE = '2021' THEN 1 ELSE 0 END) AS enrolled_2021
    FROM TABLE_A
    GROUP BY ID, YEAR, LAST_NAME, FIRST_NAME, DEGREE, CREDIT
)
SELECT 
    ce.TERM_YEAR AS YEAR,
    ce.STUDENT_ID AS ID,
    ce.LAST_NAME,
    ce.FIRST_NAME,
    ce.DEGREE,
    ce.CREDIT,
    -- 按规则优先级判断最终YEAR值
    CASE
        -- 规则3：已完成所有四门核心课程
        WHEN sc.completed_1009 = 1 AND sc.completed_1029 = 1 AND sc.completed_2011 = 1 AND sc.completed_2021 = 1 THEN 3
        -- 规则2：已完成基础课，当前注册进阶课
        WHEN sc.completed_1009 = 1 AND sc.completed_1029 = 1 AND ce.enrolled_2011 = 1 AND ce.enrolled_2021 = 1 THEN 2
        -- 规则1：当前注册基础课
        WHEN ce.enrolled_1009 = 1 AND ce.enrolled_1029 = 1 THEN 1
        -- 其他情况保留原标记（根据需求调整）
        ELSE 'N'
    END AS YEAR
FROM current_enrollments ce
JOIN student_completed_courses sc ON ce.STUDENT_ID = sc.STUDENT_ID
ORDER BY ce.STUDENT_ID;

代码分步解释 #

1. 统计学生已完成课程（CTE：student_completed_courses） #

这个部分从TABLE_B提取每个学生的过往学期已完成课程：

• 用GROUP BY STUDENT_ID按学生分组
• 用CASE + MAX标记每个学生是否完成了指定课程：只要在2024之前的学期修过该课程（不管成绩是否为空，因为过往学期的课程都属于已完成），就标记为1，否则为0

2. 统计学生当前注册课程（CTE：current_enrollments） #

这个部分从TABLE_A提取每个学生的当前学期选课情况：

• 同样按学生分组，保留学生的基本信息（姓名、学位等）
• 用CASE + MAX标记当前学期是否注册了指定课程：只要选了该课程就标记为1，否则为0

3. 结合数据判断YEAR值（主查询） #

• 用JOIN把两个CTE关联起来，让每个学生的已完成课程和当前选课信息对应上
• 用CASE WHEN按规则优先级判断最终YEAR值：
  • 先判断规则3（最高优先级）：如果已经完成所有四门课，直接设为3
  • 再判断规则2：如果完成了基础课（1009/1029）且当前注册进阶课（2011/2021），设为2
  • 最后判断规则1：如果当前注册基础课，设为1
  • 其他情况保留原标记N（你可以根据需求调整）

验证预期结果 #

运行这段代码后，会得到你想要的结果：

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