知识点:累计求和;窗口函数
分析题目:成绩为A的同学有2人,成绩为B的同学有2人,成绩为C的同学有2人,成绩为D的同学有1人。所以得A的同学最差排名是2,得B的同学最差排名是2+2=4,以此类推,得C的同学最差排名是2+2+2=6,得D的同学最差排名是2+2+2+1=7。因此说,最差排名实际上就是对成绩高于自己的人数做累加。
方法一:自联结
SELECT C1.grade,SUM(C2.number) AS t_rank
FROM class_grade C1,class_grade C2
WHERE C1.grade >= C2.grade
GROUP BY C1.grade //一直不对,看了题解之后加上的
ORDER BY C1.grade;
或者
SELECT
t1.grade,
SUM(t2.number) AS t_rank
FROM
class_grade AS t1
LEFT JOIN class_grade AS t2
ON t2.grade <= t1.grade
GROUP BY t1.grade
ORDER BY t1.grade;
方法二&#x