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

select
    t1.id,
    t1.job,
    t1.score,
    t1.t_rank
from (
    -- 每个岗位里进行排名打标
    select
        id,
        job,
        score,
        row_number() over(partition by job order by score desc) t_rank
    from grade
) t1
inner join (
    -- 每个岗位的总数的一半
    select 
        job,
        round(count(job) / 2, 1) cn_half
    from grade
    group by job
) t2
on t1.job = t2.job 
where 
case 
    when substr(t2.cn_half,length(t2.cn_half)) then t_rank = round(t2.cn_half + 0.5,0) 
    else t_rank in (round(t2.cn_half,0),round(t2.cn_half + 1,0))
end 
order by t1.id;