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

select g.id, g.job, g.score, g.r
from
(
select id, job, score, rank() over(partition by job order by score desc) r from grade 
) g
join 
(
select job, 
    round(case when count(job)%2=1 then (1+count(job))/2 else count(job)/2 end) start,
    round(case when count(job)%2=1 then (1+count(job))/2 else count(job)/2+1 end) end
from 
(
select id, job, score, rank() over(partition by job order by score desc) r from grade 
)t
group by job
)t1
on g.job = t1.job
where g.r >= t1.start and g.r <= t1.end
order by g.id