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

这题看出来dense_rank应该就没啥问题了

select id, name ,score
from 
    (select g.id,
        l.name,
        g.score,
        dense_rank() over(partition by g.language_id order by score desc) as lang_sub_rank
    from grade g
    left join language l 
    on g.language_id = l.id) tmp
where lang_sub_rank <=2
order by name asc, score desc, id asc;