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

with a as (
    select 'A' as grade, 0 as number
    union all
    select 'B' as grade, 0 as number 
    union all
    select 'C' as grade, 0 as number
    union all
    select 'D' as grade, 0 as number
    union all
    select 'E' as grade, 0 as number
    union all 
    select * from class_grade
),
b as (
    select grade, sum(number) as number
    from a
    group by grade
),
c as (
    select grade, sum(number) over(order by grade rows between unbounded preceding and current row) as worst, case when grade = 'A' then 1 else sum(number) over(order by grade rows between unbounded preceding and 1 preceding) + 1 end as best, sum(number) over() as total
    from b 
    where grade in (select grade from class_grade)
)
select grade
from c
where total % 2 = 1 and (total+1)/2 between best and worst
union
select grade
from c
where total % 2 = 0 and ((total/2 between best and worst) or (total/2+1 between best and worst))
order by grade

不知道自己在写啥了