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

select
a.id
,a.job
,a.score
,a.t_rank
from ( # 这个子表目的是:按照job分区，对score进行降序，找到不同工作内部的分数排序
        select
        id
        ,job
        ,score
        ,dense_rank() over(partition by job order by score desc) t_rank
        from grade
     ) a
join ( # 这个子表目的是：找到每个工作中位数的始末位置，然后匹配上一子表的排序后的位置
        select
        job
        ,case when medium = round(medium, 0) then round(medium,0) else round(medium+0.5,0) end as start # medium = round(medium,0)判断是否medium是小数,进而可以确定count(*)是奇数还是偶数，然后start和end分情况讨论
        ,case when medium = round(medium, 0) then round(medium+1,0) else round(medium+0.5,0) end as end
        from (
                select
                job
                ,count(*)/2 medium
                from grade
                group by 1
             ) e
     ) b
on a.job = b.job
where a.t_rank in (b.start, b.end) # 判断排序的位置是否是中位数的始末位置
order by id; # 按id升序排列