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

with t1 as (
    select floor(((select sum(number) from class_grade)+1)/2) as thr
    union all
    select ceil(((select sum(number) from class_grade)+1)/2)
), t2 as (
    select grade, number, sum_
    from (
        select grade, number,
        sum(number) over (order by grade) as sum_
        from class_grade
    ) temp
)
select distinct grade
from (
    select t1.thr, min(sum_) as flag
    from t1 join t2 on t2.sum_>=t1.thr
    group by t1.thr
) temp2 join t2 on temp2.flag=t2.sum_
order by grade