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

先准备好两个表
一个是上一题的 两个中位数的位置在哪里
一个是带排名的表
两个表用where连接一下就好了

select g.id,
    g.job,
    g.score,
    g.t_rank
from
    (select id,
        job,
        score,
        row_number() over(partition by job order by score desc) as t_rank
    from grade) g,
    (select job,
        (case when count(1)%2=1 then round((count(1)+1)/2) else round(count(1)/2) end) start,
        (case when count(1)%2=1 then round((count(1)+1)/2) else round(count(1)/2)+1 end) end
    from grade
    group by job
    order by job) tmp
where g.job = tmp.job and (g.t_rank=tmp.start or g.t_rank=tmp.end)
order by g.id, g.t_rank desc;