如何编写SQL查询筛选出最新年份成绩优于往年的学生？

需求 #

仅输出那些在各自最新年份中成绩优于往年的学生信息。

现有数据表 #

student|Year|Marks
ABC|2023|98
XYZ|2021|100
MNO|2019|81
XYZ|2020|98
MNO|2018|98
ABC|2022|100

预期结果 #

XYZ|2021|100

尝试的SQL查询 #

with cte as 
  (select *, row_number() over (partition by name, year order by marks) as rn
   from table1)

select name, year, marks
from CTE
where rn = 1 

当前问题 #

上面的查询没法实现跨年份的成绩对比，筛选不出符合要求的记录。

正确解法 #

要搞定这个需求，得先找到每个学生的最新年份成绩，再判断这个成绩是不是比他所有往年的成绩都高。这里给两种可行的写法：

方法一：窗口函数实现 #

WITH student_year_stats AS (
    SELECT 
        student,
        Year,
        Marks,
        -- 拿到每个学生的最新年份
        MAX(Year) OVER (PARTITION BY student) AS latest_year,
        -- 计算每个学生往年成绩的最大值（排除最新年份）
        MAX(CASE WHEN Year != MAX(Year) OVER (PARTITION BY student) THEN Marks END) OVER (PARTITION BY student) AS max_prev_marks
    FROM table1
)
SELECT student, Year, Marks
FROM student_year_stats
-- 只留最新年份的记录，且成绩要比往年最高还高
WHERE Year = latest_year AND Marks > max_prev_marks;

方法二：子查询实现 #

SELECT t1.student, t1.Year, t1.Marks
FROM table1 t1
-- 先定位到每个学生的最新年份记录
WHERE t1.Year = (SELECT MAX(Year) FROM table1 WHERE student = t1.student)
-- 再判断该记录的成绩是否高于该学生所有往年的成绩
AND t1.Marks > (SELECT MAX(Marks) FROM table1 WHERE student = t1.student AND Year < t1.Year);

这两种写法都能得到预期结果，核心逻辑就是先锁定每个学生的最新记录，再和他自己的历史成绩做对比。

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