mysql5.7 实现分组后组内排序功能 ROW_NUMBER() OVER (PARTITION BY)

在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;
1
2
3
4
5
6
7

表里面的数据如下：
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
1
2
3

结果：

SQL解释：

1. 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
1
2
3

和一个分组的sql类似，需要多加一个变量即可。
但有时候( 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
1
2
3
4

实际上如果想要全部的序号都更新，只需要多在外面包一层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
1
2
3
4
5
6

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦