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

-- 6: 3 and 4
-- 7: 4 and 4
-- 12: 6 and 7

with temp0 as (
    select sum(number) as total_number
    from class_grade
), temp1 as (
    select floor((total_number+1)/2) as first_rk,ceiling((total_number+1)/2) as second_rk
    from temp0 t0 
), temp2 as (
    select grade,sum(number)over(order by grade asc) as rk
    from class_grade
), temp3 as (
    select grade,rk,ifnull(lag(rk,1)over(order by grade asc)+1,1) as before_rk
    from temp2 t2 
)

select *
from
(
    select grade
    from temp3 t3 inner join temp1 t1
    on t1.first_rk >= t3.before_rk and t1.first_rk <= t3.rk
    union
    select grade
    from temp3 t3 inner join temp1 t1
    on t1.second_rk >= t3.before_rk and t1.second_rk <= t3.rk
) as t order by grade asc;