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

select
    id
    ,g1.job
    ,score
    ,t_rank
from
    (
        select 
            *
            ,row_number()over(partition by job order by score desc) as t_rank
        from
            grade
    )g1
    left join
    (
        select
            job
            ,count(job) as cnt
        from
            grade
        group by
            job
    )g2
    on
    g1.job = g2.job
where
    abs((cnt + 1)/2 - t_rank) <= 0.5
order by
    id