题解 | 考试分数(五)
考试分数(五)
https://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0513
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限制就可以得到所需结果。
