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

【方法一】
关键点1
扩充原表的字段，字段1【row_number】 按job分类用row_number升序排序；字段2【count】按job分类计数
关键点2
筛选位于中位数的信息：abs(t1.s_rank-(t1.num+1)/2)<1

select id,job,score,s_rank
from 
(select *
        ,(row_number()over(partition by job order by score desc))as s_rank
        ,(count(score)over(partition by job))as num
        from grade)t1
where abs(t1.s_rank-(t1.num+1)/2)<1
order by id;

【方法二】
关键点1
用自联结：g表用row_number扩充了排序列；m表是求中位数的开始和结束的位数，用floor和ceiling
关键点2
筛选位于中位数的信息：rk between m1 and m2,或abs(rk-(m1+m2)/2)<1

select id,g.job as job, score, rk
from (select id,job,score,
          row_number() over(partition by job order by score desc
            ) as rk
      from grade 
      ) g
left join (select job,floor((count(id) + 1)/ 2) as m1,
               ceiling((count(id) + 1)/ 2) as m2
           from grade
           group by job
           ) m
on g.job = m.job
where rk between m1 and m2 
order by id;