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

with base as 
(
    select
    id,
    job,
    score,
    row_number() over(partition by job order by  score desc) t_rank,
    count(*) over(partition by job) job_cnt,
    case when (count(*) over(partition by job))%2 in (0)  then 0
    else 1
    end as mod_label
    from grade
)

select
id,job,score,t_rank 
from
(
    select id,job,score,t_rank,
    case when  mod_label in (0) then  job_cnt/2 end as get_1,
    case when  mod_label in (0) then  1+job_cnt/2 end as get_2,
    case when  mod_label in (1) then  (job_cnt+1)/2 end as get_3
    from base
)
where (t_rank=get_1 or t_rank=get_2 or  t_rank=get_3)
order by id asc

本题核心即为正确的找到 “中位数”，有partition下的总数为奇数或者偶数两种情况

1. 后者对应code line 20-21
2. 前者对应code line 22

因此问题变成怎样判断partition下的奇偶，见code line 9-10
然后通过where条件取出对应的行数即可