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

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
)
select grade, sum(number) over(order by grade rows between unbounded preceding and current row) as t_rank
from b 
where grade in (select grade from class_grade)

自己觉得很酷，由于担心某个成绩没有人，自己构建一下完整成绩单