题解 | #查找在职员工自入职以来的薪水涨幅情况#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
好像有点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
;
