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

1.上一题找到中位数：
select job,
round(count(score)/2) start,
case count(score)%2
when 1 then round(count(score)/2)
when 0 then round(count(score)/2)+1
end as end
from grade g
GROUP by job;t2

2.显示每个id在所在岗位的排名t_rank
select id,job,score,ROW_NUMBER()over(partition by job order by score desc) t_rank
from grade;t1

3.把t1表中t_rank等于中位数（start和end）的id 的信息查出来即可
select t2.id,t2.job,t2.score,t_rank
from (select id,job,score,ROW_NUMBER()over(partition by job order by score desc) t_rank
from grade)t2
join(select job,
round(count(score)/2) start,
case count(score)%2
when 1 then round(count(score)/2)
when 0 then round(count(score)/2)+1
end as end
from grade g
GROUP by job)t1
on t2.job=t1.job
where t_rank=start or t_rank=end
order by t2.id