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