每个岗位分数排名前2名的用户dense_rank over

select 
    id,
    name,
    score
from (
    select 
        g.id,
        name,
        score,
        dense_rank() over(partition by name order by score desc) rk
    from grade g
    left join language l on g.language_id=l.id
 ) t
where rk<3   
order by name,score desc,id

dense_rank() over(partition by ... order by ...)