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

问题：请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

问题拆解：1.上一题先求出中位数的表格r1

(SELECT job, CASE WHEN COUNT(score)%2=1 THEN COUNT(score)/2+0.5 
			 ELSE      COUNT(score)/2   END AS s,
             CASE WHEN COUNT(score)%2=1 THEN COUNT(score)/2+0.5 
             ELSE      COUNT(score)/2+1 END AS e
FROM grade
GROUP BY job) r1

2.制作有排名的表格r2

(SELECT id, job, score, RANK() OVER(PARTITION BY job ORDER BY score DESC)
 t_rank FROM grade
GROUP BY id, job, score) r2

3.链接两个表，使r1的s和e分别等于r2的排名，提取需要的字段

4.orderby即可

以上。

select r2.id, r1.job, r2.score, r2.t_rank from
(select job, case when count(score)%2=1 then count(score)/2+0.5 else count(score)/2 end as s,
            case when count(score)%2=1 then count(score)/2+0.5 else count(score)/2+1 end as e
from grade
group by job) r1
join (select id, job, score, rank() over(partition by job order by score desc) t_rank from grade
group by id, job, score) r2
on r1.job=r2.job 
where r1.s=r2.t_rank or r1.e=r2.t_rank
order by r2.id