题解 | #考试分数(五)#

用前面一道题的中位数序号(t2属于个人爱好，帮我更容易写下面嘿嘿嘿)

with t1 as (
    select job,floor((count(*)+1)/2) as 'start',floor((count(*)+2)/2) as 'end'
    from grade
    group by job
),
t2 as (
    select job,t1.start as t_rank
    from t1
    union 
    select job,t1.end as t_rank
    from t1
    group by job
)
select a.id,t2.job,a.score,t_rank
from (
    select *,row_number() over(partition by job order by score desc) as rk
    from grade
) a
join t2
on a.job = t2.job
where rk = t_rank
order by a.id