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

思路：利用row_number() 确定排名，然后根据排名顺序确定中位数位置范围

#group by 自带排序，默认升序
select d.job,
(case mod(max(rk), 2)
when  0 then round(max(rk)/2,0) 
when  1 then round(max(rk)/2, 0)
end) as start,
(case mod(max(rk), 2)
when 0 then round(max(rk)/2,0) + 1
when 1 then round(max(rk)/2, 0)
end) as end
from (select *, (row_number() over(partition by job order by job)) as rk from grade) d group by d.job;