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

select
    id,
    a.job,
    score,
    t_rank
from
    (select 
        id,
        job,
        score,
        row_number() over(partition by job order by score desc) as t_rank
    from 
        grade ) a
left join 
    (
    select 
        job,
        floor(if(count(*)%2=0, count(*)/2, (count(*)+1)/2)) as start,
        floor(if(count(*)%2=0, count(*)/2+1, (count(*)+1)/2)) as end
    from 
        grade 
    group by
        job
    order by
        job
    ) b
on a.job = b.job and a.t_rank >= b.start and a.t_rank <= b.end
where b.job is not null
order by id