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

-- 求结果排序
SELECT 
c.id,d.`name`,c.score
FROM
language d, 
(
SELECT a.id,a.language_id,a.score,a.number
FROM(
-- 按排名去top2的language
SELECT
id,language_id,score,DENSE_RANK() OVER (PARTITION BY language_id ORDER BY score DESC) AS number
FROM
grade
)a
WHERE
a.number <= 2
)c
WHERE
c.language_id = d.id
ORDER BY
d.name,c.score DESC,c.id