题解 | 最差是第几名(二)

##写的太复杂了，排行里的写法比较精简
##计算中位数的序号，一个或两个，根据序号找出>=序号的第一个成绩，根据辅助列最终确认成绩

select grade
from (

select grade,number,psum,tsum,mid1,mid2,f1,f2, row_number()over(partition by f1 order by grade) as rn1, row_number()over(partition by f2 order by grade) as rn2
from (
select grade,number,psum,tsum,mid1,mid2,
        case when psum>=mid1 then 1 else 0 end as f1,
        case when psum>=mid2 then 1 else 0 end as f2
from 
(
select grade,psum,tsum, number,case when  tsum% 2 =0 then 
                     tsum/2  
                   else 
                      ceil(tsum/2) end  as mid1
                      ,
                      case when  tsum% 2 =0 then 
                     tsum/2 +1  end as mid2
from 
(
select 
  grade , number , sum(number) over(order by grade) as psum
         ,sum(number) over() as tsum
from class_grade
) t 
)t
) t
)t  where (f1 =1 and rn1 =1 ) or(f2=1 and rn2 =1)




##排行里的这个写法多精妙啊！！！
select grade from (
    select grade
            ,(select sum(number) from class_grade) as 'total'
            ,sum(number) over(order by grade) a
            ,sum(number) over(order by grade desc) b
     from class_grade) t1
where a >= total/2 and b >=total/2
order by grade