题目
解答
方法一:
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 -- 不同工作(job)里面的每个人的信息与排名
on (A.job=B.job and B.t_rank between A.start and A.end)
order by B.id
-- 要提取中位数的信息,恰好A有中位数的信息,那么联立A,B表,并且当工作(job)相同
--并且, B的排名(rank)在A表的中位数位置之间,那么就表明这个信息是中位数的信息,最后联立
方法二(优化):
select b.*
from ( select id,job,score,row_number() over (partition by job order by score desc) as t_rank
from grade ) as b ,(select job,floor((count(id)+1)/2) as mid_start,floor((count(id)+2)/2) as mid_end
from grade
group by job ) a
where b.job = a.job
and b.t_rank between a.mid_start and a.mid_end
order by b.id;
方法三(优化):
select a.id,a.job,a.score,b.t_rank
from(
select id,job,score,ROW_NUMBER() over (partition by job order by score desc) as ranks
from grade ) a
join (
select job,floor((count(score)+1)/2) as t_rank
from grade
group by job
union
select job,floor((count(score)/2)+1) as t_rank
from grade
group by job ) b
on a.job = b.job
and a.ranks = b.t_rank
order by a.id;