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

弯路饶了不少，最差——设其为每个等级的最后一个，则就是个累加过程。
1、case when + 子查询

select grade,
case grade when 'A' then (select sum(number) from class_grade where grade <= 'A')
           when 'B' then (select sum(number) from class_grade where grade <= 'B')
           when 'C' then (select sum(number) from class_grade where grade <= 'C')
           when 'D' then (select sum(number) from class_grade where grade <= 'D')
           else (select sum(number) from class_grade where grade <= 'E')
end as t_rank
from class_grade
order by grade;

2、开窗函数 sum() over() 更简便

select grade, sum(number) over(order by grade) as t_rank
from class_grade 
order by grade;

3、联结

SELECT a.grade,SUM(b.number) AS t_rank
FROM class_grade a
inner join class_grade b
ON a.grade >= b.grade
GROUP BY a.grade
ORDER BY a.grade asc;