题解 | #考试分数(五)#查询每组中位数位置上的记录

/* 1、
with t as(
    select *,
        row_number() over(partition by job order by score desc) t_rank
    from grade
),
t2 as(
    select job,
        floor((max(t_rank)+1)/2) start,
        floor((max(t_rank)+2)/2) end
    from t
    group by job
)

select id,job,score,t_rank
from t
left join t2 using(job)
where t_rank=start or t_rank=end
order by id

2、 */
select id,
    job,
    score,
    t_rank
from(
    select *,
        row_number() over(partition by job order by score desc) t_rank,
        count(score) over(partition by job) num
    from grade
) t
where abs(t_rank-(num+1)/2)<1
order by id