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

窗口函数计数，对于总数为奇数和偶数时要分类讨论，用union函数去掉重复项，如果有空的就不取

select 
    case 
    when sum(number)%2=1 then (select grade
        from(
            select grade,sum(number) over(order by grade asc) as rk
            from class_grade
        )a
        where rk>=ceil(sum(number)/2)
        limit 1
    )
    when sum(number)%2=0 then(select grade
        from(
            select grade,sum(number) over(order by grade asc) as rk
            from class_grade
        )b
        where rk>=ceil(sum(number)/2)
        limit 1
    )
    end as grade
from class_grade
union 
select *
from(
    select
    case
    when sum(number)%2=0 then(select grade
            from(
                select grade,sum(number) over(order by grade asc) as rk
                from class_grade
            )b
            where rk>=ceil((sum(number)/2)+1)
            limit 1
        )
        end as grade
    from class_grade
    )a
where grade is not null