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

#找中位数的范围
select
    job
    ,floor(mid_r)  _start#中位数左边界
    ,floor(if(mid_r=floor(mid_r),mid_r,ceiling(mid_r))) _end#中位数右边界
from (
         select
            distinct
             job
            ,(max(r)over(partition by job)+1)/2 mid_r#注意要分组讨论
         from (
                  select
                      *
                       ,row_number()over(partition by job order by score) r
                  from grade#各岗位分数升序
              ) a#各岗位中位数 注意是整数或小数
     ) b
order by 1#按job升序
#floor(?)向下取整
#ceiling(?)向上取整
#max(?)over(partition by ?)分组取最大
#row_number()over(partition by ? desc)分组降序，序号连续不重复