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

#所有grade信息+排名信息
select
    id
    ,b.job
    ,score
    ,t_rank
from (
         select
             *
              ,row_number() over (partition by job order by score desc) t_rank
         from grade#各岗位排名
         ) b
    left join (
                select
                    distinct
                    job
                    ,max(t_rank+1)over(partition by job)/2 mid_r
                from (
                         select
                             *
                              ,row_number() over (partition by job order by score desc) t_rank
                         from grade#各岗位排名
                     ) a#各岗位排名中位数 注意是整数或小数
                ) c
        on c.job=b.job
where t_rank=floor(mid_r)#中位数左边界
    or t_rank=floor(if(floor(mid_r)=mid_r,mid_r,ceiling(mid_r)))#中位数右边界
order by 1#按id升序