题解 | #考试分数(三)#

SELECT a.id,l.name,a.score
FROM
    language AS l
    JOIN (
        SELECT id,language_id,score,
            dense_rank() over (PARTITION BY language_id ORDER BY score DESC
            ) AS rank_num
        FROM grade
    ) a ON l.id = a.language_id
WHERE rank_num <= 2
ORDER BY l.name,a.score DESC,a.id;

首先用dense_rank()给每个岗位进行排序。注意这里排序不用跳过位次，故使用dense_rank。

注：跳过位次使用rank，每个位次唯一使用row_number

 dense_rank() over (PARTITION BY language_id ORDER BY score DESC) AS rank_num

在取出位次前两位

WHERE rank_num <= 2

排序

ORDER BY l.name,a.score DESC,a.id;

完整代码如下

SELECT a.id,l.name,a.score
FROM
    language AS l
    JOIN (
        SELECT id,language_id,score,
            dense_rank() over (PARTITION BY language_id ORDER BY score DESC
            ) AS rank_num
        FROM grade
    ) a ON l.id = a.language_id
WHERE rank_num <= 2
ORDER BY l.name,a.score DESC,a.id;