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

with sc_rk as (
select id,language_id,score,dense_rank() over(partition by language_id order by score desc) as rk
from grade
    group by id,language_id
)

select a.id,name,score
from sc_rk a 
left join language b 
on a.language_id=b.id
where rk<=2
group by a.id,name
order by name,score desc,id