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

先给原表打上正序序号标签 
再打上逆序序号标签，算出每个工作的计数
然后正序大于行数的一半逆序大于行数的一半就是中位数了

select id,job,score,t_rank
from 
(
select grade.*,dense_rank() over(partition by job order by score desc) as t_rank
        ,dense_rank() over(partition by job order by score ) as t_rank1
        ,count(score) over(partition by job ) as t_sum
from grade  
) as g
where t_rank>=(t_sum/2)
and   t_rank1>=(t_sum/2)
order by id