题解 | #考试分数(五)#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
select distinct a.id, a.job, a.score, a.t from
(select *, dense_rank() over (partition by job order by score desc) t from
grade) a
join
(select job,
case when count(job)%2=1 then (count(job)+1)/2 else count(job)/2 end as start,
case when count(job)%2=1 then (count(job)+1)/2 else count(job)/2+1 end as end
from grade
group by job ) b
on a.job=b.job
and (a.t=b.start
or a.t=b.end)
order by id
