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

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
),
tmp2 as (
    select job, if((i+m) % 2 =0, round((i+m)/2), floor((i+m)/2)) as begin, if((i+m) % 2 =0, round((i+m)/2), ceil((i+m)/2)) as end from tmp
order by job
)

select g.id, g.job, g.score, r from grade g inner join tmp2 on g.job = tmp2.job inner join (select id, row_number() over(partition by job order by score desc) as r from grade) t3 on g.id = t3.id
where r=tmp2.begin or r=tmp2.end
order by g.id asc

over(partition by job order by score desc) 对付有数值的室友别忘了asc或者desc