属实有点绕

with t as (
        select *,ROW_NUMBER()over(partition by job order by score desc) as t_rank,count(1)over(partition by job) as num
        from grade
)
select id,job,score,t_rank
from t
where (id,t_rank) in 
(select id,case when mod(num,2)=1 then (num+1)/2
                else num/2
                end as 'rank'
 from t 
 union
 select id,case when mod(num,2)=0 then num/2+1
                else 0
                end as 'rank'
 from t 
)
order by id