题解 | #牛客每个人最近的登录日期(四)#

使用score子查询

select g.id,l.name,g.score
from grade g,language l
where g.language_id=l.id and g.score in (
select distinct g.score from grade g2
where g.language_id=g2.language_id and g.score<=g2.score
group by g.score
having count(distinct g2.score)<=2
)
order by l.name,g.score desc,g.id

使用language_id,score子查询，先找出符合情况的language_id和score，再从原表中筛选符合条件的行；更加麻烦不推荐使用

select g2.id,l.name,g2.score from grade g2,language l
where g2.language_id=l.id and (g2.language_id,g2.score) in
(select g.language_id,g.score
from grade g,grade g1
where g.language_id=g1.language_id and g.score<=g1.score
group by g.language_id,g.score
having count(distinct g1.score)<=2)
order by l.name,g2.score desc,g2.id

取巧方法，三表联立，使用用户id分组；但这种方法仅仅适合本题，当不给出id，只要求给出各个分组内的前两名，这个方法就不适用了

select g1.id,name,g1.score
from grade g1,grade g2,language l
where g1.language_id=g2.language_id and g1.score<=g2.score and g1.language_id=l.id
group by g1.id
having count(distinct g2.score)<3
order by name asc,g1.score desc,g1.id asc

使用窗口函数

select id,name,score from
(select g.id,l.name,g.score,dense_rank() over(partition by l.name order by score desc) as t_rank
from grade g,language l
where g.language_id=l.id
order by l.name,score desc,id)a
where t_rank=1 or t_rank=2