题解 | 丝滑 极致享受

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