题解 | 考试分数(四)

# 先按照语言进行排序
# 对于每种语言，总序号数是偶数的，start为总数/2，end为总数/2+1,；总数序号为奇数的，start为ceil(总数/2)，end也是
select
    a.job as job,
    round(case 
        when mod(count(*),2) = 1 then ceil(count(*)/2)
        when mod(count(*),2) = 0 then count(*)/2 
    end) as start,
    round(case 
        when mod(count(*),2) = 1 then ceil(count(*)/2)
        when mod(count(*),2) = 0 then count(*)/2 + 1
    end) as end
from
    (select
        *,
        dense_rank() over(partition by job order by score desc) as ranks
    from
        grade) a
group by 
    a.job