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

select f.id,f.job,f.score,f.t_rank
from
(
select distinct n1.id,n1.job,n1.score,n1.t_rank
from(
select id,job,score,row_number() over(partition by job order by score desc) as t_rank
from grade) as n1 join
(select n.job,case when mod(n.num,2) =0  then round(n.num/2,0) else round((n.num+1)/2,0) end as Start
from (select job,count(id) as num
from grade
group by job) as n) as n2 on n1.t_rank = n2.Start and n1.job = n2.job

union

select distinct n11.id,n11.job,n11.score,n11.t_rank
from(
select id,job,score,row_number() over(partition by job order by score desc) as t_rank
from grade) as n11 join
(select n1.job,case when mod(n1.num,2) =0 then round(n1.num/2 + 1,0) else round((n1.num+1)/2,0) end as End
from (select job,count(id) as num
from grade
group by job) as n1) as n12 on n11.t_rank = n12.end and n11.job = n12.job
    ) as f