窗口函数 永远滴神！

select  t1.id,
    t1.job, score,
    t_rank from ( select  id, job, score, dense_rank() over (partition by job order by score desc ) t_rank from grade
)t1 inner join ( select  job, round(count(id)/2) start, round((count(id)+1)/2) end  from grade group by job )t2 on t1.t_rank in (t2.start,t2.end) and t1.job = t2.job order by t1.id;