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

# 先找排名，在根据排名挑选中位数

select
a.id
,a.job
,a.score
,a.ranking
from(
select
a.id
,a.job
,a.score
,a.total
,a.ranking
,round(if(a.total%2=0,total/2,(total+1)/2),0) as qian
,round(if(a.total%2=0,total/2+1,(total+1)/2),0) as hou
from(
select
a.id
,a.job
,a.score
,b.total as total
,row_number() over(partition by a.job order by a.score desc) as ranking
from grade a
join
(
select a.job,count(*) as total from grade a group by a.job
) b
on a.job=b.job
) a
) a
where a.ranking in(qian,hou)
order by a.id
;