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

高赞写法是有问题的，正确写法如下：
with a as (
select grade,
sum(number) over(order by grade) as cnt,
row_number() over(order by grade) as ran
from class_grade
),
b as (
select a.grade,
case when a.ran=1 then 0 else b.cnt+1 end as start,
a.cnt as end
from a left join a b on a.ran-1=b.ran
),
c as (select
case when sum(number)%2=0 then sum(number)/2 else (sum(number)-1)/2 +1 end vid
from class_grade
union
select
case when sum(number)%2=0 then sum(number)/2 +1 else (sum(number)-1)/2 +1 end vid
from class_grade
)
select grade from b join c
where vid>=b.start and vid <=b.end group by grade order by grade;