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

  • 考察窗口函数的使用