SQL实战76.考试分数(五)

题目

在这里插入图片描述

解答

方法一:

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;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值