题解 | 考试分数(五)

select id,job,score,t_rank
from (select id,job,score,row_number() over (partition by job order by score DESC) as t_rank,
row_number() over (partition by job order by score) as t_rank_d
from grade) a
where t_rank=floor((t_rank+t_rank_d)/2) or t_rank_d=floor((t_rank+t_rank_d)/2)
order by id

正序排和逆序排之后，相加结果即为总数+1，利用这个性质，如果总数为奇数，中位数正序序号=中位数逆序序号=（正+逆）/2；如果总数为偶数，两个中位数位置上分别为：正数序号（正+逆）/2，逆序序号=（正+逆）/2。因此正排，倒排，并用where限制就可以得到所需结果。