SQL如何从同一日期列的两个不同年份分别筛选Top5数据

你当前写的SQL无法实现分年度取Top5，核心问题有两个：

• 没有按年度做分区排名，全局ORDER BY只会把两年数据混在一起排序，无法做到每个年份单独截断前5条
• 表关联逻辑没有绑定成绩和对应学年的关系，MAX(I.abgeschlossen)取的是学生最长的结业年份，会把其他学年的成绩都归到这个年份下，数据统计口径本身就有偏差
• 直接写死A.note = 1的过滤条件，会丢失成绩排序梯度，就算排序也没法准确取到前5（如果当年满分人数不足5人，结果会缺数据）

用窗口函数按学年分区、按成绩排序生成排名，再筛选每个分区排名前5的记录即可，一次查询就能拿到两年各前5的结果，性能也更好：

WITH student_score_rk AS (
    SELECT
        S.vorname,
        S.nachname,
        YEAR(I.abgeschlossen) AS Abschlussjahr,
        AVG(A.note) AS avg_note, -- 如果统计单门最优成绩就换成MIN(A.note)，统计总分换成SUM
        -- 按学年分区，成绩从优到劣排序（德国计分制1为最优，所以升序排）
        ROW_NUMBER() OVER(
            PARTITION BY YEAR(I.abgeschlossen)
            ORDER BY A.note ASC
        ) AS rk
    FROM student S
    INNER JOIN inskription I
        ON S.matnr = I.student
    INNER JOIN absolvierung A
        ON S.matnr = A.student
        -- 注意：这里要补成绩和学年的关联条件，避免跨学年成绩串数据，请把pruefungsdatum替换成你成绩表里实际的考试日期字段
        AND YEAR(I.abgeschlossen) = YEAR(A.pruefungsdatum)
    WHERE YEAR(I.abgeschlossen) IN (2016, 2017)
    GROUP BY S.vorname, S.nachname, YEAR(I.abgeschlossen)
)
SELECT vorname, nachname, Abschlussjahr, avg_note
FROM student_score_rk
WHERE rk <= 5
ORDER BY Abschlussjahr DESC, rk ASC;

适配调整说明 #

• 如果遇到同分学生需要全部保留，把ROW_NUMBER()替换成DENSE_RANK()即可，不会出现同分学生被意外排除的情况
• 如果你只需要统计拿到满分（note=1）的学生，直接在CTE里加回WHERE A.note = 1的过滤条件即可，分区排名逻辑依然生效
• 如果你用的是不支持窗口函数的老版本数据库（比如MySQL 5.x），可以用关联子查询计数的方式实现，性能略差但逻辑等效：

SELECT
    S.vorname,
    S.nachname,
    YEAR(I.abgeschlossen) AS Abschlussjahr,
    A.note
FROM student S
INNER JOIN inskription I ON S.matnr = I.student
INNER JOIN absolvierung A ON S.matnr = A.student
WHERE YEAR(I.abgeschlossen) IN (2016, 2017)
AND (
    SELECT COUNT(DISTINCT S2.matnr)
    FROM student S2
    INNER JOIN inskription I2 ON S2.matnr = I2.student
    INNER JOIN absolvierung A2 ON S2.matnr = A2.student
    WHERE YEAR(I2.abgeschlossen) = YEAR(I.abgeschlossen)
    AND A2.note <= A.note -- 成绩优于等于当前学生的人数不超过5个，即当前学生是前5
) <= 5
ORDER BY Abschlussjahr DESC, A.note ASC;

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