题解 | #最差是第几名(一)#
最差是第几名(一)
http://www.nowcoder.com/practice/ae5e8273e73b4413823b676081bd355c
弯路饶了不少，最差——设其为每个等级的最后一个，则就是个累加过程。
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;
查看7道真题和解析
