题解 | #考试分数(五)# 考虑在中位数的位置上出现重复score的情况
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
-
row_number 排序值从小到大,依次排列(重复的数随机排序)
-
dense_rank 相同数据,并列保存,不存在断值(一直连续，重复的只占一个序号)
-
rank 相同数据并列保存,下一个值跳值(断续，重复的占多个序号)
这道题我感觉很多人写题解的时候都没有考虑中位数附近大量重复成绩时的情况，如果只是只有数的数列，那中位数就是它，重复与否并不影响，但是这里是一行，他们只是score和job一样，id不同的，所以在我的概念中应该把他们都输出。（不过好像题目也没考虑到这点）
所以我的思路是这样的：
-
先找到中位数到底在哪个范围，这个是很确定的，求总量再算即可
select job, case when mod(num,2)=1 then round((num+1)/2) else round(num/2) end as start, case when mod(num,2)=0 then round(num/2+1) else round((num+1)/2) end as end from (select job,count(id) num from grade group by job) t
-
之后为各类排序，排序方法用row_number，用它能够能保证中位数那个编号的人的score确实就是中位数实际的score，这样我们就可以联结上面找到的编号，找到目标score
select *, row_number() over(partition by job order by score desc) as t_rank_all from grade
-
根据上面两个联结选出每个job对应的合适的score
select a.job, a.score, a.t_rank_all from ( select *, row_number() over(partition by job order by score desc) as t_rank_all from grade ) as a, ( select job, case when mod(num,2)=1 then round((num+1)/2) else round(num/2) end as start, case when mod(num,2)=0 then round(num/2+1) else round((num+1)/2) end as end from (select job,count(id) num from grade group by job) t ) as b where a.job = b.job and (a.t_rank_all = b.start&nbs***bsp;a.t_rank_all = b.end)
-
最后根据找到的score联结grade表，输出所有合适的行
select g.id, g.job, g.score, c.t_rank_all as t_rank from grade as g, ( select a.job, a.score, a.t_rank_all from ( select *, row_number() over(partition by job order by score desc) as t_rank_all from grade ) as a, ( select job, case when mod(num,2)=1 then round((num+1)/2) else round(num/2) end as start, case when mod(num,2)=0 then round(num/2+1) else round((num+1)/2) end as end from (select job,count(id) num from grade group by job) t ) as b where a.job = b.job and (a.t_rank_all = b.start&nbs***bsp;a.t_rank_all = b.end) ) as c where g.job = c.job and g.score = c.score order by id;
