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

select id,job,score,rk as t_rank
from
(
    select id,g.job,score,start,end,row_number() over(partition by g.job order by score desc) rk
    from grade g 
    left join
    (select job,if(count(*)%2=1,round(count(*)/2,0),round(count(*)/2,0)) as start,if(count(*)%2=1,round(count(*)/2,0),round(count(*)/2+1,0)) as end
    from grade
    group by job
    order by job) j 
    on g.job=j.job
) b
where start=rk or end=rk
order by id

思路很简单，中位数的寻找上一题已经写过了，所以可以直接复制过来

select job,if(count(*)%2=1,round(count(*)/2,0),round(count(*)/2,0)) as start,if(count(*)%2=1,round(count(*)/2,0),round(count(*)/2+1,0)) as end
from grade
group by job
order by job

然后是排名问题，用窗口函数

select id,g.job,score,start,end,row_number() over(partition by g.job order by score desc) rk
from grade g 

然后两表连接一下

最后where筛选一下就完成了