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

这道题最大的难点在于：

1）对 “如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者” 这一条件的理解。 2）题目要求的结果，每一行的ranking是不可能重复的，所以ROW_NUMBER()相较于DENSE_RANK()肯定是更优的

第一次写的原代码如下，使用了两层子查询：

SELECT t2.tag, t2.uid, t2.ranking
FROM
(
    SELECT t.tag, t.uid, 
           ROW_NUMBER() OVER (PARTITION BY t.tag ORDER BY t.max_s DESC, t.min_s DESC, t.uid DESC) AS ranking
    FROM
    (
        SELECT i.tag, r.uid, MAX(r.score) AS max_s, MIN(r.score) AS min_s
        FROM examination_info AS i INNER JOIN exam_record AS r
        ON i.exam_id = r.exam_id
        GROUP BY i.tag, r.uid
    ) AS t
) AS t2
WHERE t2.ranking <=3;

最里一层子查询，目的就在于得出一个表t，针对每一个考试类别 & 每一个考生（GROUP BY i.tag, r.uid），得出他们的历史最高分和历史最低分（ MAX(r.score), MIN(r.score) ）；随后，在第二层子查询，则是基于表t，再使用ROW_NUMBER()来提取出：

“（重新）按照考试类别来分组，并且再按照【历史最高分降序、历史最低分降序、UID降序】这一条件来决定，各位考生在每一个考试类别下的最终排名”

但是，以上代码还有一个可以优化的地方，参考了@盐咸咸的回答，发现第二层和第一层的可以直接合并，即：

SELECT t3.tag, t3.uid, t3.ranking
FROM
(
    SELECT i.tag, r.uid, MAX(r.score) AS max_s, MIN(r.score) AS min_s,
           ROW_NUMBER() OVER (PARTITION BY i.tag ORDER BY MAX(r.score)DESC, MIN(r.score) DESC, r.uid DESC) AS ranking
           /*这里写max(a.score)和min(a.score)是为了帮助理解窗口函数里的ORDER BY 后面的内容*/
    FROM examination_info AS i INNER JOIN exam_record AS r
    ON i.exam_id = r.exam_id
    GROUP BY i.tag, r.uid
) AS t3
WHERE t3.ranking <= 3;