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

select 
grade
from(
    select 
    grade,
    lag(sum,1,0) over(order by grade) as lag_sum,
    sum,
    n1,
    n2
    from(
        
        select grade,
        sum(number)over(order by grade) sum,
        floor(((select sum(number) from class_grade)+1)/2) as n1,
        ceil(((select sum(number) from class_grade)+1)/2) as n2
        from class_grade
    )a

)b
where (n1>lag_sum and n1<=sum) or (n2>lag_sum and n2<=sum)
order by grade

最直观的方法去看中位数落在哪个区间。