题解 | 考试分数(三)

-- 一看到排名，这题就确定要用窗口函数了，所以首先要根据分数去语言内排名
WITH ranking_l AS(
    SELECT
        id,
        language_id,
        score,
        DENSE_RANK() OVER(partition by language_id order by score DESC) AS ranking
    FROM grade
)
-- 第二步，将上面的cte和语言表相连接获取信息(排名前两位的)
SELECT t1.id,t2.name,t1.score
FROM ranking_l t1
JOIN language t2
ON t1.language_id = t2.id
WHERE t1.ranking < 3
ORDER BY t2.name,t1.score DESC,t2.id