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

示例

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

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

单字段分组

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

SELECT
	t.*,
	row_number() over(PARTITION BY t.COMPANY ORDER BY t.CREATE_TIME) AS ROW_NO 
FROM
	user t 
WHERE
	t.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 —— 按照公司分组、创建时间排序，并对每一组内的行进行编号

SELECT
	t.*,
	@num := IF(@field_1 <=> t.COMPANY, @num + 1, 1) AS ROW_NO,
	@field_1 := t.COMPANY AS FIELD_1
FROM
	user t,
	(SELECT @num := 0, @field_1 := NULL) a 
WHERE
	t.ID > 0 
ORDER BY
	t.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 —— 按照公司和部门分组、创建时间排序，并对每一组内的行进行编号

SELECT
	t.*,
	row_number() over(PARTITION BY t.COMPANY, t.DEPT ORDER BY t.CREATE_TIME) AS ROW_NO 
FROM
	user t 
WHERE
	t.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 —— 按照公司和部门分组、创建时间排序，并对每一组内的行进行编号

SELECT
	t.*,
	@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 
FROM
	user t,
	(SELECT @num := 0, @field_1 := NULL, @field_2 := NULL) a 
WHERE
	t.ID > 0 
ORDER BY
	t.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 ...) 函数的分组排序编号效果，可能会影响查询的性能和稳定性，应该谨慎使用。如果不是迫不得已没办法，我会用它？