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

# 在grade表后添加两个新列，一个表示组内排名一个表示组内记录数
select *, row_number()over(partition by job order by score desc) rank_s,
count(score)over(partition by job)
from grade
# 在窗口函数添加了新列的子查询基础上，筛选出各个岗位分数的中位数位置上的所有grade信息
select id, job, score, rank_s t_rank
from (
    select *, row_number()over(partition by job order by score desc) rank_s,
    count(score)over(partition by job) num
    from grade
) t1
where (case when num%2=0 then rank_s=num/2 or rank_s=num/2+1 when num%2<>0 then rank_s=(num+1)/2 end) = 1
order by id;