SQL新手求助：如何查询学生考试最终状态（通过优先，否则取最新）

嘿，作为SQL新手遇到这种场景确实容易卡壳，我来帮你搞定这个需求！

首先明确你的核心需求：

• 对于每个学生，只要有过Passed记录，最终状态就显示Passed（不管之后有没有其他状态）
• 没有Passed记录的学生，取他们最新时间的状态

我给你两种可行的方案，你可以根据自己的SQL环境选择：

方案一：使用窗口函数（推荐，简洁高效） #

这种方式只需要扫描一次表，性能更优，逻辑也清晰：

WITH student_summary AS (
    SELECT 
        Student_ID,
        Status,
        Time_stamp,
        -- 标记该学生是否存在Passed记录
        MAX(CASE WHEN Status = 'Passed' THEN 1 ELSE 0 END) OVER (PARTITION BY Student_ID) AS has_passed,
        -- 按时间倒序给每个学生的记录排名，最新的记录排第1
        ROW_NUMBER() OVER (PARTITION BY Student_ID ORDER BY Time_stamp DESC) AS record_rank
    FROM results
)
SELECT 
    Student_ID,
    CASE 
        WHEN has_passed = 1 THEN 'Passed'
        ELSE Status
    END AS Final_Status
FROM student_summary
WHERE record_rank = 1;

逻辑解释： #

1. 先用CTE student_summary 给每条记录做两个标记：
   • has_passed：通过窗口函数MAX+CASE，只要该学生有一条Passed记录，这个值就会变成1，否则是0
   • record_rank：按学生分组，把记录按时间从新到旧排序，最新的记录排名为1
2. 外层查询只保留每个学生排名第1的记录（也就是最新的那条），然后通过CASE判断：如果has_passed是1，直接显示Passed；否则就用这条最新记录的状态。

方案二：用UNION ALL拆分逻辑 #

如果你对窗口函数不太熟悉，也可以用这种拆分的方式，逻辑更直观：

-- 第一部分：所有有过Passed记录的学生，直接返回Passed
SELECT Student_ID, 'Passed' AS Final_Status
FROM results
WHERE Status = 'Passed'
GROUP BY Student_ID

UNION ALL

-- 第二部分：没有Passed记录的学生，取他们最新的状态
SELECT r.Student_ID, r.Status AS Final_Status
FROM results r
INNER JOIN (
    -- 先找出没有Passed记录的学生，以及他们的最新考试时间
    SELECT Student_ID, MAX(Time_stamp) AS latest_exam_time
    FROM results
    WHERE Student_ID NOT IN (SELECT DISTINCT Student_ID FROM results WHERE Status = 'Passed')
    GROUP BY Student_ID
) latest_records ON r.Student_ID = latest_records.Student_ID 
                 AND r.Time_stamp = latest_records.latest_exam_time;

逻辑解释： #

1. 第一部分：筛选出所有有Passed记录的学生，分组后直接返回Passed状态
2. 第二部分：先找出没有Passed记录的学生，再获取他们的最新考试时间，最后关联回原表拿到对应时间的状态
3. 用UNION ALL把两部分结果合并，因为两部分的学生ID没有重叠，不会重复

验证结果 #

用你的测试数据运行上面的查询，会得到完全符合你预期的结果：

• Student_ID 1 → Passed
• Student_ID 2 → Passed
• Student_ID 3 → Failed
• Student_ID 4 → Passed

你之前的查询只筛选了Status = "Passed"的记录，所以漏掉了那些没有通过但需要显示最新状态的学生，这两个方案都能完美覆盖所有情况~

内容的提问来源于stack exchange，提问作者Damon Daisley Wilson