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

select id,
       job,
       score,
       rk as t_rank
from(
    select id,
           job,
           score,
           dense_rank()over(partition by job order by score desc) as rk,##在job中的排名
           row_number()over(partition by job order by score) as rn, ##当前job的自然序号
           round((count(1)over(partition by job))/2,0) as start, ## 中位数开始位置
           round((count(1)over(partition by job)+1)/2,0) as end ##中位数结束位置
    from grade
)a
where (rn=start or rn=end) ##取中位数位置上的结果
order by id