题解 | #最差是第几名(一)#三种方法

问题描述：请你写出一个SQL查询，如果一个学生知道了自己综合成绩以后，最差是排第几名? 结果按照grade升序排序，以上例子查询如下:

方案1：采用窗口函数SUM() OVER()

SELECT grade,SUM(number) OVER(ORDER BY grade ASC) AS t_cnt
FROM class_grade
ORDER BY grade ASC;

方案2：采用CASE表达式+子查询

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) 
                         END AS t_cnt
FROM class_grade
ORDER BY grade ASC;

方案3：采用自联结

SELECT c1.grade,SUM(c2.number) AS t_rank
FROM class_grade c1
CROSS JOIN class_grade c2
ON c1.grade >= c2.grade
GROUP BY c1.grade
ORDER BY c1.grade ASC;