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

# 找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者
SELECT *
FROM(
    SELECT a.tag, a.uid,
        ROW_NUMBER() OVER (PARTITION BY a.tag ORDER BY max_score DESC, min_score DESC, uid DESC) AS ranking
    FROM(
        SELECT i.tag, r.uid, 
            MAX(r.score) AS max_score, 
            MIN(r.score) AS min_score
        FROM examination_info AS i LEFT JOIN exam_record AS r ON i.exam_id=r.exam_id
        GROUP BY i.tag, r.uid
        ) AS a
    ) AS b
WHERE b.ranking<=3;