题解 | #考试分数(五)#多种方法

# 思路1：将工资从小到大排序，奇数个，返回中间值，偶数个，返回中间两个值
# SELECT t.id,t.job,t.score,t.posn
# from (
#   SELECT id,job,score
#   ,row_number()over(partition by job order by score desc) posn
#   ,count(*)over(partition by job) cnt
#   from grade
# ) as t
# where posn in (floor(cnt/2)+1,if(mod(cnt,2)=0,floor(cnt/2),floor(cnt/2)+1))
# order by t.id

# 思路2：所谓中位数，即无论升序还是倒序都处于中间的数
with t_rank as
(
    select *,
       count(score) over(partition by job) as total,
       row_number() over(partition by job order by score) as a, #升序序号
       row_number() over(partition by job order by score desc) as b #逆序序号
    from grade
)

select id,job,score,b
from t_rank
where a>=total/2 and b>=total/2 # 正序和逆序的位置都>=总个数/2
order by id