各班级学生成绩测试表 select * from TMP_A;
实现目的: 按照班级分类后按照分数倒序排序
采用mysql变量简单实现,SQL如下:
SELECT a.stu_id,a.point,
IF( @className = a.class_name,@rank := @rank + 1,@rank :=1 ) AS rank1,
(@className := a.class_name) class_name
FROM TMP_A a, ( select @rank :=0,@className := NULL ) b
ORDER BY a.class_name, a.point DESC ;
最终展示结果如下: