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

子查询中连接grade和language两个表并用dense_rank()对score从大到小排序并将排名列为一个新字段ranking。然后从子查询中选出id, name, score并限制ranking不大于2，最后根据题目要求进行排序。

select temp.id, temp.name, temp.score from 
(select g.id, l.name, g.score, 
dense_rank() over (partition by l.name order by g.score desc) as ranking
from grade as g, language as l
where g.language_id = l.id) as temp
where temp.ranking <= 2
order by temp.name, temp.score desc, temp.id