# 每个岗位排名前2
with
t1 as(
    select
        grade.id as id,
        name,
        score,
        dense_rank()over(partition by name order by score desc) as ranks
    from
        grade
        left join language on grade.language_id=language.id
)

select
    id,
    name,
    score
from
    t1
where
    ranks in (1,2)
order by
    name,
    score desc,
    id