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

本题是 #考试分数(四)# 的升级版。
1、根据 #考试分数(四)# 得出位于job对应的中位数位置

select 
    job,
    ceil(count(*) / 2) as start,
    ceil(count(*) / 2 + 0.5) as end
from grade
group by job 
order by job

2、使用开窗函数获得各job对应score的排名

select
    *,
    row_number() over(partition by job order by score desc) as t_rank
from grade

3、使用多条件选择，从1中找出与2中的 job、t_rank 相同的 job、start 或者 job、end

select
    id,
    job,
    score,
    t_rank
from (
        select
            *,
            row_number() over(partition by job order by score desc) as t_rank
        from grade
      ) as t
where (job, t_rank) in (
        select
            job,
            start
        from (
               select 
                    job,
                    ceil(count(*) / 2) as start,
                     ceil(count(*) / 2 + 0.5) as end
                from grade
                group by job 
                order by job
        ) as g1 )
or (job, t_rank) in (
        select
            job,
            end
        from (
               select 
                   job,
                   ceil(count(*) / 2) as start,
                    ceil(count(*) / 2 + 0.5) as end
                from grade
                group by job 
                order by job
        ) as g2)
order by id