题解 | 每类试卷得分前3名

SELECT z.tag, z.uid, z.ranking
FROM(
    SELECT ei.tag, er.uid, ROW_NUMBER() OVER(PARTITION BY ei.tag ORDER BY 
    MAX(er.score) DESC, MIN(er.score) DESC, er.uid DESC) AS ranking
    FROM exam_record AS er
    LEFT OUTER JOIN examination_info AS ei
    ON er.exam_id = ei.exam_id
    GROUP BY ei.tag, er.uid) AS z # 涉及到聚合函数，故需按字段进行分组
WHERE z.ranking <= 3; # 窗口函数的排名结果不能直接在where中使用，需使用嵌套查询的方式作为新表传入主查询