做题的时候提交了很多遍,其实也可以不用去匹配区间,根据总数的奇偶性分布计算也是可行的方案
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