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

select r.id, r.job, r.score, r.t_rank
from 
(select co.job job, co.c number, 
case when c%2=1 then round((c+1)/2,0) else round(c/2,0) end as start, 
case when c%2=1 then round((c+1)/2,0) else round((c/2)+1,0) end as end
from (select job, count(job) c from grade group by job) co) bc
left join
(select id, job, score, row_number()over(partition by job order by score desc) t_rank from grade) r
on bc.job=r.job
where r.t_rank=bc.start or r.t_rank=bc.end
order by r.id