MySQL5.7 实现类似 MySQL8.0 中 row_number() over(partition by ... order by ...) 函数的分组排序编号效果

示例

现在这里有一张用户表 user，里面包含以下字段：ID 主键、USERNAME 用户名、PASSWord 密码、COMPANY 公司、DEPT 部门、CREATE_TIME 创建时间。

单字段分组

MySQL 8.0 —— 按照公司分组、创建时间排序，并对每一组内的行进行编号

SELECTt.*,row_number() over(PARTITioN BY t.COMPANY ORDER BY t.CREATE_TIME) AS ROW_NO FROMuser t WHEREt.ID > 0;

+----+----------+----------+---------+------+---------------------+--------+| ID | USERNAME | PASSWORD | COMPANY | DEPT | CREATE_TIME         | ROW_NO |+----+----------+----------+---------+------+---------------------+--------+|  2 | 李四     | 123456   | 1       | 1    | 2023-02-10 13:12:29 |      1 ||  6 | 赵九     | 123456   | 1       | 1    | 2023-02-21 13:16:24 |      2 ||  5 | 老八     | 123456   | 1       | 2    | 2023-02-25 13:14:39 |      3 ||  3 | 王五     | 123456   | 2       | 1    | 2023-02-20 13:13:48 |      1 ||  1 | 张三     | 123456   | 2       | 1    | 2023-02-22 13:12:04 |      2 ||  4 | 老六     | 123456   | 2       | 2    | 2023-02-24 13:14:18 |      3 |+----+----------+----------+---------+------+---------------------+--------+

MySQL 5.7 —— 按照公司分组、创建时间排序，并对每一组内的行进行编号

SELECTt.*,@num := IF(@field_1 <=> t.COMPANY, @num + 1, 1) AS ROW_NO,@field_1 := t.COMPANY AS FIELD_1FROMuser t,(SELECT @num := 0, @field_1 := NULL) a WHEREt.ID > 0 ORDER BYt.COMPANY,t.CREATE_TIME;

+----+----------+----------+---------+------+---------------------+--------+---------+| ID | USERNAME | PASSWORD | COMPANY | DEPT | CREATE_TIME         | ROW_NO | FIELD_1 |+----+----------+----------+---------+------+---------------------+--------+---------+|  2 | 李四     | 123456   | 1       | 1    | 2023-02-10 13:12:29 |      1 | 1       ||  6 | 赵九     | 123456   | 1       | 1    | 2023-02-21 13:16:24 |      2 | 1       ||  5 | 老八     | 123456   | 1       | 2    | 2023-02-25 13:14:39 |      3 | 1       ||  3 | 王五     | 123456   | 2       | 1    | 2023-02-20 13:13:48 |      1 | 2       ||  1 | 张三     | 123456   | 2       | 1    | 2023-02-22 13:12:04 |      2 | 2       ||  4 | 老六     | 123456   | 2       | 2    | 2023-02-24 13:14:18 |      3 | 2       |+----+----------+----------+---------+------+---------------------+--------+---------+

说明：这个 sql 语句中使用用户变量 @num 和 @field_1，分别表示当前行的排序编号和前一个分组的 COMPANY 字段的值。

在 SELECT 子句中，使用了 IF 函数来判断当前行是否与前一行属于同一分组，如果是，将当前行的排序编号加 1，否则，将排序编号重置为 1。

在 ORDER BY 子句中，需要将分组字段 COMPANY 放在前面，将排序字段 CREATE_TIME 放在后面。

多字段分组

MySQL 8.0 —— 按照公司和部门分组、创建时间排序，并对每一组内的行进行编号

SELECTt.*,row_number() over(PARTITION BY t.COMPANY, t.DEPT ORDER BY t.CREATE_TIME) AS ROW_NO FROMuser t WHEREt.ID > 0;

+----+----------+----------+---------+------+---------------------+--------+| ID | USERNAME | PASSWORD | COMPANY | DEPT | CREATE_TIME         | ROW_NO |+----+----------+----------+---------+------+---------------------+--------+|  2 | 李四     | 123456   | 1       | 1    | 2023-02-10 13:12:29 |      1 ||  6 | 赵九     | 123456   | 1       | 1    | 2023-02-21 13:16:24 |      2 ||  5 | 老八     | 123456   | 1       | 2    | 2023-02-25 13:14:39 |      1 ||  3 | 王五     | 123456   | 2       | 1    | 2023-02-20 13:13:48 |      1 ||  1 | 张三     | 123456   | 2       | 1    | 2023-02-22 13:12:04 |      2 ||  4 | 老六     | 123456   | 2       | 2    | 2023-02-24 13:14:18 |      1 |+----+----------+----------+---------+------+---------------------+--------+

MySQL 5.7 —— 按照公司和部门分组、创建时间排序，并对每一组内的行进行编号

SELECTt.*,@num := IF(@field_1 <=> t.COMPANY && @field_2 <=> t.DEPT, @num + 1, 1) AS ROW_NO,@field_1 := t.COMPANY AS FIELD_1,@field_2 := t.DEPT AS FIELD_2 FROMuser t,(SELECT @num := 0, @field_1 := NULL, @field_2 := NULL) a WHEREt.ID > 0 ORDER BYt.COMPANY,t.DEPT,t.CREATE_TIME;

+----+----------+----------+---------+------+---------------------+--------+---------+---------+| ID | USERNAME | PASSWORD | COMPANY | DEPT | CREATE_TIME         | ROW_NO | FIELD_1 | FIELD_2 |+----+----------+----------+---------+------+---------------------+--------+---------+---------+|  2 | 李四     | 123456   | 1       | 1    | 2023-02-10 13:12:29 |      1 | 1       | 1       ||  6 | 赵九     | 123456   | 1       | 1    | 2023-02-21 13:16:24 |      2 | 1       | 1       ||  5 | 老八     | 123456   | 1       | 2    | 2023-02-25 13:14:39 |      1 | 1       | 2       ||  3 | 王五     | 123456   | 2       | 1    | 2023-02-20 13:13:48 |      1 | 2       | 1       ||  1 | 张三     | 123456   | 2       | 1    | 2023-02-22 13:12:04 |      2 | 2       | 1       ||  4 | 老六     | 123456   | 2       | 2    | 2023-02-24 13:14:18 |      1 | 2       | 2       |+----+----------+----------+---------+------+---------------------+--------+---------+---------+

说明：这个 SQL 语句中使用用户变量 @num 、@field_1 和 @field_2，分别表示当前行的排序编号、前一个分组的 COMPANY 字段的值和 DEPT 字段的值。

在 SELECT 子句中，使用了 IF 函数来判断当前行是否与前一行属于同一分组，如果是，将当前行的排序编号加 1，否则，将排序编号重置为 1。

在 ORDER BY 子句中，需要将分组字段 COMPANY 和 DEPT 放在前面，将排序字段 CREATE_TIME 放在后面。

注意

使用用户变量来实现类似 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 函数的分组排序编号效果，可能会影响查询的性能和稳定性，应该谨慎使用。如果不是迫不得已没办法，我会用它？

来源地址：https://blog.csdn.net/weixin_49523761/article/details/129214488