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

#嵌套
select
b.id
,b.job
,b.score
,b.t_rank
from
    (select
    id
    ,job
    ,score
    ,row_number()over(partition by job order by score desc)as t_rank
    from grade) b #窗口函数排序
join
    (select
    job
    ,case
    when mod(count(job),2)=1 then round(count(job)/2,0)
    when mod(count(job),2)=0 then floor(count(job)/2)
    end as start
    , case
    when mod(count(job),2)=1 then round(count(job)/2,0)
    when mod(count(job),2)=0 then floor(count(job)/2)+1
    else 0
    end as end
    from
        (select
        job
        ,score
        from grade
        group by 1,2) a
    group by 1
    order by 1) c #找中位数位置
on b.job=c.job
where b.t_rank = c.start or b.t_rank=c.end #序号=中位数位置
order by id;