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

select xx.id,xx.job,xx.score,xx.t_rank from
(select 
g.id,
g.job,
g.score,
ROW_NUMBER()over(partition by job order by score desc) as t_rank,
x.start,
x.end
FROM grade g
LEFT JOIN (SELECT job, 
    floor(( count(*) + 1 )/ 2 ) AS start, 
    floor(( count(*) + 2 )/ 2 ) AS end 
FROM grade 
GROUP BY job 
ORDER BY job)x on x.job = g.job)xx
WHERE xx.t_rank <= xx.end and xx.t_rank>=xx.start
ORDER BY xx.id