题解 | #考试分数(五)#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
用row_number() 和dense_rank()得出按job分组后的序号和排名，作为主表d:
select *,row_number() over(partition by job order by score) rank1
,dense_rank() over(partition by job order by score desc) rank2
from grade g
将主表与上一题的start和end分别联结，取出需要的列，生成最终表c
select id,d.job,d.score,rank1,rank2,start,end from
(select *,row_number() over(partition by job order by score) rank1
,dense_rank() over(partition by job order by score desc) rank2
from grade g)d
left join
(select job,floor((count(*)+1)/2) as start from grade group by job) a
on d.job=a.job and d.rank1=a.start
left join
(select job,floor((count(*)+2)/2) as end from grade group by job) b
on d.job=b.job and d.rank1=b.end
对c表限定条件：c.start is not null or c.end is not null，取出规定字段即可
select c.id,c.job,c.score,c.rank2 as t_rank from
(select id,d.job,d.score,rank1,rank2,start,end from
(select *,row_number() over(partition by job order by score) rank1
,dense_rank() over(partition by job order by score desc) rank2
from grade g)d
left join
(select job,floor((count(*)+1)/2) as start from grade group by job) a
on d.job=a.job and d.rank1=a.start
left join
(select job,floor((count(*)+2)/2) as end from grade group by job) b
on d.job=b.job and d.rank1=b.end)c
where c.start is not null or c.end is not null
order by c.id
