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

select 
    id,
    job,
    score,
    t_rank
from (select *,row_number()over(partition by job order by score desc) t_rank from grade) s
where (job, t_rank) in (select
            job,
            case when count(*)%2 = 1 then ceil(count(*)/2)
            else round(count(*)/2)
            end start
        from 
            grade
        group by job)
or (job, t_rank) in (select
    job,
    case when count(*)%2 = 0 then round(count(*)/2+1)
    end as "end"
from 
    grade
group by job)
order by id

注意：select中定义的别名不能在where中被使用