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

select
    id,
    job,
    score,
    t_rank
from (
    select
        id,
        job,
        score,
        count(score) over (partition by job) as total,
        row_number() over(partition by job order by score desc) as t_rank
    from grade
) as g1
where 
(
    case when total % 2 = 0 then t_rank = total/2 or t_rank = total/2 + 1
    else t_rank = CEILING(total/2)
    end )
order by id