SQL查询求助：如何获取每位学生Exam=Yes的最新成绩记录

问题说明 #

需求是获取每位学生中Exam='Yes'的最新记录，但原SQL语句逻辑有误：它对学生的所有记录按日期倒序排名后取第一条，再筛选Exam='Yes'，这会导致那些最新记录不是考试的学生被排除，无法得到预期结果。

原SQL语句：

WITH ranked_messages AS (
select Student_number,Exam,Mark,Date_of_exam,
ROW_NUMBER() OVER (PARTITION BY Student_number ORDER BY Date_of_exam DESC) as rnt
FROM new_table 
)
SELECT * FROM ranked_messages WHERE rnt = 1  and Exam = 'Yes'

修正方案 #

需要针对每个学生的考试记录单独排名，先筛选出Exam='Yes'的记录，再进行分区排名，这样就能精准获取每位学生最新的考试成绩：

WITH ranked_exams AS (
    SELECT 
        Student_number, 
        Exam, 
        Mark, 
        Date_of_exam,
        ROW_NUMBER() OVER (PARTITION BY Student_number ORDER BY Date_of_exam DESC) AS rnt
    FROM new_table 
    WHERE Exam = 'Yes'  -- 先过滤出考试记录再排名
)
SELECT Student_number, Exam, Mark, Date_of_exam
FROM ranked_exams 
WHERE rnt = 1;

逻辑解释 #

1. 先通过WHERE Exam = 'Yes'过滤出所有有效考试记录，确保只处理符合要求的数据
2. 按Student_number分区，每个学生的考试记录按Date_of_exam倒序排名，rnt=1即为该学生最新的考试记录
3. 最后提取排名第一的记录，即可得到每位学生的最新考试成绩，完全匹配预期结果

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