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

感觉这种题很怪

select 
    id,job,score,rk
from 
(
    select 
        id,job,score,rk,case when top mod 2 = 0 then if(rk=round(top/2) or rk=(round(top/2) + 1) ,1,0) else if(rk=(floor(top/2)+1),1,0) end as flag
    from 
    (
        select 
            id,job,score,rk,max(rk) over(partition by job) as top
        from 
        (
            select 
                id,job,score,dense_rank() over(partition by job order by score desc) as rk
            from grade
        ) t1 
    ) t2 
) t3 where flag = 1
order by id