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

select
    job,
    case
        when count(job)%2=1 then round(max(r_n)/2,0)
        when count(job)%2=0 then round(max(r_n)/2,0)
    end as start,
    case
        when count(job)%2=1 then round(max(r_n)/2,0)
        when count(job)%2=0 then round(max(r_n)/2+1,0)
    end as end
from
(
    select
        job,
        row_number() over (partition by job order by score) as r_n
    from grade
) as g1
group by job