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

select 
    job,
    case 
        when (t1.rk +1) % 2 = 0
        then 
            round((t1.rk + 1)/2,0)
        else 
            round(t1.rk/2,0)
        end start,
    case 
        when (t1.rk +1) % 2 = 0
        then 
            round((t1.rk + 1)/2,0)
        else 
            round(1 + (t1.rk/2),0) 
        end 'end'
from(
    select 
        *,
        dense_rank() over(partition by job order by score asc) rk,
        max(score) over(partition by job) max_score
    from 
        grade
    ) t1
where 
    t1.score = t1.max_score