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

with r1 as (select id,job,score,row_number()over(partition by job order by score desc) t_rank 
from grade
),r2 as (
    select job,if(mod(count(id),2)=0,floor(count(id)/2),floor(count(id)/2)+1) t1,if(mod(count(id),2)=0,floor(count(id)/2)+1,null) t2
    from grade
    group by job    
)
select r1.id,r1.job,r1.score,r1.t_rank
from r1 join r2 on r1.job=r2.job
where r1.t_rank=r2.t1 or r1.t_rank=r2.t2
order by r1.id