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

/*
排序粉数前两名 dense_rank()over(partition by language order by score desc)
重复的继续排，前两名可能有三位数所以不能用rank
rank 1,1,3,4
row_number 1,2,3,4
dense_rank 1,1,2,3
order by name, score desc
*/
select
a.id, b.name, a.score
from
(select *, dense_rank()over(partition by language_id order by score desc)rk
from grade)a join language b on a.language_id = b.id
where a.rk <3
order by name, score desc