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

select grade from(
    select grade, case when floor((rk2 + 1) / 2) between rk0 and rk1 or ceil((rk2 + 1) / 2) 
        between rk0 and rk1 then 1 else 0 end as t
    from(
        select grade, number, sum(number)  over(
            order by grade
            ) - number + 1 as rk0,
            sum(number) over(
                order by grade
            ) as rk1, 
            sum(number) over() as rk2
        from class_grade
        order by grade
    ) as tb
) as tb1
where t = 1
order by grade