考试分数(五)
考试分数(五)
http://www.nowcoder.com/questionTerminal/b626ff9e2ad04789954c2132c74c0512
通过考试分数(四) https://blog.nowcoder.net/n/64e552f2359141f1bc730f00dad84cb9 我们知道，中位数的位置信息，也就是升序之后的排名信息，得到A表:
(select job, cast((count(id)+1)/2 AS INTEGER) as 'start' , (cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end)) as 'end' from grade group by job) A
(select g1.*, ( select count(distinct g2.score) from grade g2 where g2.score>=g1.score and g1.job=g2.job) as rank from grade g1 ) B大概得到的情况如下:
所以 B表里面有所有的信息，但是要提取中位数的信息，恰好A有中位数的信息，那么联立A，B表，并且当工作(job)相同并且，B的排名(rank)在A表的中位数位置之间，那么就表明这个信息是中位数的信息，最后联立得:
select B.* from (select job, cast((count(id)+1)/2 AS INTEGER) as 'start' , (cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end)) as 'end' from grade group by job) A JOIN (select g1.*, ( select count(distinct g2.score) from grade g2 where g2.score>=g1.score and g1.job=g2.job) as rank from grade g1 ) B on (A.job=B.job and B.rank between A.start and A.end) order by B.id
select B.* from (SELECT job,FLOOR((COUNT(*)+1)/2) AS `start`,FLOOR((COUNT(*)+1)/2)+if(COUNT(*) % 2=1,0,1) AS `end` FROM grade GROUP BY job) A JOIN (select g1.*, ( select count(distinct g2.score) from grade g2 where g2.score>=g1.score and g1.job=g2.job) as t_rank from grade g1 ) B on (A.job=B.job and B.t_rank between A.start and A.end) order by B.id
投递牛客等公司6个岗位 >
