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

with tmp as (
    select job, min(r) as i, max(r) as m from (select job, score, row_number() over(partition by job order by score desc) as r from grade) as tb
    group by job
)

select job, if((i+m) % 2 =0, round((i+m)/2), floor((i+m)/2)), if((i+m) % 2 =0, round((i+m)/2), ceil((i+m)/2)) from tmp
order by job

round和floor和ceil都可以取整，floor是地板，向下取整，ceil是天花板，细胞壁，向上取整