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

做题的时候提交了很多遍，其实也可以不用去匹配区间，根据总数的奇偶性分布计算也是可行的方案

with base as 
(select grade,IFNULL(sum(number) over(order by grade rows 
 between UNBOUNDED preceding and 1 preceding ),0)+1 as buttom,
 sum(number) over(order by grade) as top
 from class_grade
 order by grade)

select grade from base 
where (select sum(number) as total from class_grade)/2 
       between buttom and top
       or (select sum(number) as total from class_grade)/2+1
       between buttom and top
       or top between (select sum(number) as total from class_grade)/2
       and (select sum(number) as total from class_grade)/2+1