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

1.内连接+dense_rank() over(...)窗口函数
select id,name,score
from (select t1.*,t2.name,dense_rank() over(partition by t1.language_id order by t1.score desc) rk
from grade t1 join language t2 on t1.language_id=t2.id ) t
where rk in (1,2)
order by name asc,score desc,id asc;

2.关联子查询
select t1.id,t2.name,score
from grade t1,language t2
where t1.language_id=t2.id
and 2>=(select count(distinct t3.score) from grade t3 where t1.language_id=t3.language_id and t1.score<=t3.score)
order by name asc,score desc,id asc;