题解 | 考试分数(五)

select * from 
(select *,row_number()over(partition by job order by score desc) t_rank from grade ) e
where (e.job,e.t_rank) in (select job,
case when count(*)over(partition by job)%2 = 0
then count(*)over(partition by job) div 2
else (count(*)over(partition by job) div 2) +1
end start
from grade)
or (e.job,e.t_rank) in(select job,
case when count(*)over(partition by job)%2 = 0
then (count(*)over(partition by job) div 2) +1
else (count(*)over(partition by job) div 2) +1
end end
from grade)
order by id
以上是常规思路，先用窗口对排序后的分数排名， 再计算中位数位置，再where去匹配

另一种方案，数学解决思路
select id,job,score,s_rank
from 
(select *
        ,(row_number()over(partition by job order by score desc))as s_rank
	    ,(count(score)over(partition by job))as num
        from grade)t1
where abs(t1.s_rank-(t1.num+1)/2)<1
order by id;