题解 | #考试分数(五)#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
select r.id, r.job, r.score, r.t_rank
from
(select co.job job, co.c number,
case when c%2=1 then round((c+1)/2,0) else round(c/2,0) end as start,
case when c%2=1 then round((c+1)/2,0) else round((c/2)+1,0) end as end
from (select job, count(job) c from grade group by job) co) bc
left join
(select id, job, score, row_number()over(partition by job order by score desc) t_rank from grade) r
on bc.job=r.job
where r.t_rank=bc.start or r.t_rank=bc.end
order by r.id
from
(select co.job job, co.c number,
case when c%2=1 then round((c+1)/2,0) else round(c/2,0) end as start,
case when c%2=1 then round((c+1)/2,0) else round((c/2)+1,0) end as end
from (select job, count(job) c from grade group by job) co) bc
left join
(select id, job, score, row_number()over(partition by job order by score desc) t_rank from grade) r
on bc.job=r.job
where r.t_rank=bc.start or r.t_rank=bc.end
order by r.id