题解 | 考试分数(四)

select
distinct job,
case
    when (count(*) over(partition by job))%2=1
    then round(((count(*) over(partition by job))-1)/2+1,0)
    else round(((count(*) over(partition by job))-1)/2,0)
    end as start,
case
    when (count(*) over(partition by job))%2=1
    then round(((count(*) over(partition by job))-1)/2+1,0)
    else round((count(*) over(partition by job))/2+1,0)
    end as end
from grade
order by job