题解 | 考试分数(五)

with ranked as(
select id,
       job,
       score,
       row_number() over(partition by job order by score desc) t_rank,
       count(*) over (partition by job) cnts
from grade)
(
    select id,job,score,t_rank
    from ranked 
    where t_rank = CEIL(cnts/2) and cnts%2!=0
)
union all
(
    select id,job,score,t_rank
    from ranked
    where cnts%2=0 and (t_rank = cnts/2 or t_rank = 1+cnts/2 )
)
order by id