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

# 取出每个岗位id分数排名前二的用户，使用dense_rank()
select id, language_id, score
from (
    select *, dense_rank()over(partition by language_id order by score desc) rank_s
    from grade
) t1
where rank_s in (1, 2)
# 连接上表和language表，获取name字段
select t2.id, name, score
from (
    select id, language_id, score
    from (
        select *, dense_rank()over(partition by language_id order by score desc) rank_s
        from grade
    ) t1
    where rank_s in (1, 2)
) t2
join language l
on t2.language_id = l.id
order by name, score desc;