#考试分数(五)#所谓中位数即无论升序倒序都处于中间的数

**题目描述：**sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

**个人思路：**所谓中位数，即无论升序还是倒序都处于中间的数。(๑•̀ㅂ•́)و✧

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
order by id

2023/03/14续

工作后偶然再次逛牛客，发现这么多然对这道题解放感兴趣。看了一下这道题解法确实有不严谨的地方，同一job存在多个score相同时,这里的row_number()正逆序号可能会出现问题，再加上id排序即可解决，感谢评论指出问题

with t_rank as
(
    select *,
       count(score) over(partition by job) as total,
       row_number() over(partition by job order by score,id) as a, #升序序号
       row_number() over(partition by job order by score desc,id desc) as b #逆序序号
    from grade
)

select id,job,score,b
from t_rank
where a>=total/2 and b>=total/2
order by id