修改后的SQL，可应用与RFM分析

 1.分组查询，得到的是按科目分

SELECT  a.sname,a.sno,a.score,
 IF( @className = a.project_name,@rank := @rank + 1,@rank :=1 ) AS rank,
(@className := a.project_name) project_name
          FROM student23   a,
( select @rank :=0,@className := NULL ) b
ORDER BY a.project_name, a.score DESC ;

2.分组查询，得到的是按学生人分

 SELECT  sname,
a.sno,a.score,
 IF( @sname = a.sname,@rank := @rank + 1,@rank :=1 ) AS rank,
  a.project_name,
(@sname := a.sname) sname
          FROM student23   a,
( select @rank :=0,@sname := NULL ) b
ORDER BY a.sname, a.score DESC ;

3.改进版

 SELECT  
a.sno,a.score,
 IF( @sname = a.sname,@rank := @rank + 1,@rank :=1 ) AS rank,
  a.project_name,
(@sname := a.sname) sname
          FROM student23   a,
( select @rank :=0,@sname := NULL ) b
ORDER BY a.sname, a.score DESC ;

综上所述，mysql实现Oracle中的partition by 的效果已基本完成。如果要显示前几条，可用where rank <=条数

参考链接：https://blog.csdn.net/God_Love_luckysue/article/details/77162890