在mysql8.0以上版本有ROW_NUMBER() OVER (PARTITION BY) 函数可以进行分组并进行组内排序，但是5.7以下版本是没有这个函数，我们这时候可以利用临时变量来实现这个效果。

1个分组依据
需求示例：现在需要统计学生的各科成绩排名，就需要安装subject科目进行分组，然后按分数倒序排序。
有一个学生表如下：

CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键Id’,
`name` varchar(5) NOT NULL COMMENT ‘学生姓名’,
`subject` varchar(6) DEFAULT NULL COMMENT ‘科目’,
`score` smallint(3) DEFAULT NULL COMMENT ‘分数’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
 
表里面的数据如下：
id name subject score
1 张三 语文 77
2 李四 语文 67
3 王五 语文 85
4 张三 数学 82
5 李四 数学 67
6 王五 英语 85
7 王五 数学 85

SELECT a.NAME, a.score, a.SUBJECT, @last :=IF(@FIRST = a.SUBJECT, @last + 1, 1 ) AS rn, @FIRST := a.SUBJECT
FROM student a, ( SELECT @last := 0, @FIRST := NULL ) b
ORDER BY a.SUBJECT, a.score DESC
 
结果： 

 

 

SQL解释：

order by 后面必须是a.SUBJECT, a.score DESC。 因为先按a.SUBJECT排序，SUBJECT相同再按score字段排序。
2.if语句表示，把subject的值赋给变量@FIRST,如果@FIRST值相同，则@last = @last +1，如果不同则@last = 1。然后@FISRST = SUBJECT字段。这样就能利用变量来实现相同的SUBJECT序号自动加一。
3.查询条件中的@last 语句一定要在@FIRST语句的前面。因为第一次查询的时候第一个@FISRT = NULL, 就会设置@last= 1,然后设置@FIRST = 数学，然后第二次查询的时候@FIRST与数学相等，然后@last= @last + 1；如此类推。如果顺序调换，@FIRST字段先赋值为数学，然后再判断@last :=IF(@FIRST = a.SUBJECT, @last + 1, 1 ) 的@FIRST = a.SUBJECT条件成立 就会导致@last总是=@last + 1。最终的结果就导致组内分组排序失败。

 

 

 

2个分组依据
现在多插入几条数据，每个学生同一个科目考了多次，有多个成绩。需要按学生和科目进行分组。

SELECT a.NAME, a.SUBJECT, a.score, @last :=IF(@FIRST = a.name and @SECOND =a.SUBJECT, @last + 1, 1 ) AS rn,@FIRST := a.NAME, @SECOND :=a.SUBJECT
FROM student a, ( SELECT @last := 0, @FIRST := NULL,@SECOND := NULL ) b
ORDER BY a.NAME, a.SUBJECT, a.score DESC

 

 

但有时候( SELECT @last := 0, @FIRST := NULL,@SECOND := NULL ) b 这里面的@SECOND := NULL可能出现漏写的情况，写成了( SELECT @last := 0, @FIRST := NULL) b 就会导致第一次查询的时候序号全部都是1。这时候因为@SECOND未定义，IF(@FIRST = a.name and @SECOND =a.SUBJECT, @last + 1, 1 )这句在执行的时候总是 @last := 1 执行。但是第二次执行这个sql却发现是正确的，这是因为在当前连接中的第一次查询已经使用了@SECOND变量。

分组查询在连表查询或连表更新失败
有时候会把分组查询结果当做是一个表来连表查询或更新，但发现在操作的时候只有第一条能够更新，
例如如下sql希望把同一组的多行rn相同的数据进行更新时，会导致只更新了第一条，rn>1之后的不会更新。但是单独拿出分组的sql语句执行的时候看到的结果又是正确的。

update table1 t inner join (SELECT a.NAME, a.score, a.SUBJECT, @last :=IF(@FIRST = a.SUBJECT, @last + 1, 1 ) AS rn, @FIRST := a.SUBJECT
FROM student a, ( SELECT @last := 0, @FIRST := NULL ) b
ORDER BY a.SUBJECT, a.score DESC) t2 on t.rn = t2.rn and t.name= t2.name
set t.score = t2.score
 
实际上如果想要全部的序号都更新，只需要多在外面包一层select * 语句就行了，避免sql在执行的时候出现问题。在外面包一层select语句，把查询结果当做是一个中间表，避免mysql执行优化器在执行sql时导致的临时变量失效。
例如一下sql:

select t.name, t.subject, t.score, t.rn from (
SELECT a.NAME, a.SUBJECT, a.score, @last :=IF(@FIRST = a.name and @SECOND =a.SUBJECT, @last + 1, 1 ) AS rn,@FIRST := a.NAME, @SECOND :=a.SUBJECT
FROM student a, ( SELECT @last := 0, @FIRST := NULL,@SECOND := NULL ) b
ORDER BY a.NAME, a.SUBJECT, a.score DESC
) t
“`sql

————————————————
版权声明：本文为CSDN博主「才_先生」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_28227405/article/details/125243938