题解 | #考试分数(五)#

-- where tt.t_rank BETWEEN tt.total/2 and tt.total/2+1  ,表名tt.可指定也可以不指定;
-- job分组的分数排行: ROW_NUMBER() over(partition by job order by score desc) as t_rank 
-- 计算job分组下的score个数 count(score) over(PARTITION by job) total
-- 只要排行数t_rank 在total中位数要么t/2,要么t/2+1之间, 加上floor(t/2) 就不对,不用向下取整
select id,job ,score ,t_rank
from 
(select id,job,score,ROW_NUMBER() over(partition by job order by score desc) as t_rank ,count(score) over(PARTITION by job) as total from grade ) tt 
where tt.t_rank BETWEEN tt.total/2 and tt.total/2+1  order by id ;


-- 解法2,先算出中位数所在的位置start-end
select job ,
round((case when total%2=0 then total/2 else (total+1)/2 end ),0 ) as `start` ,
round (( case when total%2=0 then  total/2+1 else (total+1)/2 end),0) as `END`
from (Select job,count(*) as total from grade group by job ) a 
-- 嵌套得到t_rank=b.`start` or t_rank =b.`END` 
select tt.id,tt.job,tt.score,tt.t_rank from (select id,job ,score ,ROW_NUMBER() over(partition by job order by score desc)as t_rank from grade )tt left join 
(select job ,
round((case when total%2=0 then total/2 else (total+1)/2 end ),0 ) as `start` ,
round (( case when total%2=0 then  total/2+1 else (total+1)/2 end),0) as `END`
from (Select job,count(*) as total from grade group by job ) a ) b on tt.job=b.job where t_rank=b.`start` or t_rank =b.`END` order by id;