题解 | #查找在职员工自入职以来的薪水涨幅情况#

好像有点zz了这个解法orz

select r.id,r.job,r.score,r.t_rank from
(select id, job, score, row_number() over(partition by job
                                         order by score desc) t_rank from grade) r
where (r.job,r.t_rank) in (select job, 
                           case when count(id) & 1 = 1 then (count(id)+1)*1.0/2
                           else count(id)*1.0/2+0.5
                           end d
                           from grade
                           group by job)
or (r.job,r.t_rank+0.5) in (select job, 
                           case when count(id) & 1 = 1 then (count(id)+1)*1.0/2
                           else count(id)*1.0/2+0.5
                           end d
                           from grade
                           group by job)
or (r.job,r.t_rank-0.5) in (select job, 
                           case when count(id) & 1 = 1 then (count(id)+1)*1.0/2
                           else count(id)*1.0/2+0.5
                           end d
                           from grade
                           group by job)
order by id
;