题解 | #考试分数(五)#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
思路：首先找出中位数start和end的下标得到t1表 然后对原表进行窗口排序t2 再接着连接t1和t2 条件：job和start=rk 在连接t1和t2条件：job和end=rk注意可能有重复，需要用union去重
select id, t1.job, score, rk from (select job, if(count(job)%2=0,floor(count(job)/2),floor(count(job)/2)+1) as start, floor(count(job)/2)+1 as end from grade group by job order by job)t1 join (select job, id, score, row_number() over(partition by job order by score desc) as rk from grade)t2 on t1.job=t2.job and t1.start=t2.rk union select id, t1.job, score, rk from (select job, if(count(job)%2=0,floor(count(job)/2),floor(count(job)/2)+1) as start, floor(count(job)/2)+1 as end from grade group by job order by job)t1 join (select job, id, score, row_number() over(partition by job order by score desc) as rk from grade)t2 on t1.job=t2.job and t1.end=t2.rk order by id
