MySQL数据库如何生成分组排序的序号

2024年 1月 30日 62.7k 0

经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。而MySQL5.7中由于没有这类函数,该如何实现呢,下面对比MySQL8.0,列举两种情况的实现。

1、数据准备

创建一张演示表:

#创建表
CREATE TABLE users (
  id INT PRIMARY KEY,
  group_id INT,
  c_name VARCHAR(64)
);

插入演示数据:

-- 插入10行数据
INSERT INTO users VALUES (1, 1, '张三');
INSERT INTO users VALUES (2, 1, '李四');
INSERT INTO users VALUES (3, 2, '王五');
INSERT INTO users VALUES (4, 2, '赵六');
INSERT INTO users VALUES (5, 3, '钱七');
INSERT INTO users VALUES (6, 1, '周八');
INSERT INTO users VALUES (7, 2, '吴九');
INSERT INTO users VALUES (8, 3, '郑十');
INSERT INTO users VALUES (9, 1, '孙十一');
INSERT INTO users VALUES (10, 3, '李十二');

2、生成序号 

(1)使用窗口函数ROW_NUMBER()实现

在MySQL8.0中可以直接使用窗口函数ROW_NUMBER()来实现序号的生成,例如:

# 根据c_name字段进行排序生成序号
SELECT
  ROW_NUMBER() OVER (ORDER BY c_name) AS row_num,
  id,
  c_name
FROM
users;

结果如下:

+---------+----+-----------+
| row_num | id | c_name    |
+---------+----+-----------+
|       1 |  7 | 吴九      |
|       2 |  6 | 周八      |
|       3 |  9 | 孙十一    |
|       4 |  1 | 张三      |
|       5 | 10 | 李十二    |
|       6 |  2 | 李四      |
|       7 |  3 | 王五      |
|       8 |  4 | 赵六      |
|       9 |  8 | 郑十      |
|      10 |  5 | 钱七      |
+---------+----+-----------+
10 rows in set, 1 warning (0.00 sec)

图片

(2)低版本MySQL中的实现

因为在MySQL8.0版本之前无ROW_NUMBER()窗口函数,因此需要结束变量来实现。具体示例如下:

SET @row_num = 0;


SELECT
  (@row_num:=@row_num + 1) AS row_num,
  id,
 c_name
FROM
  users
ORDER BY
  c_name;

结果如下:

+---------+----+-----------+
| row_num | id | c_name    |
+---------+----+-----------+
|       1 |  7 | 吴九      |
|       2 |  6 | 周八      |
|       3 |  9 | 孙十一    |
|       4 |  1 | 张三      |
|       5 | 10 | 李十二    |
|       6 |  2 | 李四      |
|       7 |  3 | 王五      |
|       8 |  4 | 赵六      |
|       9 |  8 | 郑十      |
|      10 |  5 | 钱七      |
+---------+----+-----------+
10 rows in set, 1 warning (0.00 sec)

图片

注意:每次执行前需要将@row_num重新设置为0 ,即执行SET @row_num = 0。

3、分组后排序

(1)继续使用窗口函数ROW_NUMBER()实现

在MySQL8.0中可以继续使用窗口函数ROW_NUMBER()来实现分组排序的功能,例如:

SELECT
  id,
  group_id,
  c_name,
  ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id) AS row_num
FROM
  users
ORDER BY
  group_id, id;

运行结果如下:

+----+----------+-----------+---------+
| id | group_id | c_name    | row_num |
+----+----------+-----------+---------+
|  1 |        1 | 张三      |       1 |
|  2 |        1 | 李四      |       2 |
|  6 |        1 | 周八      |       3 |
|  9 |        1 | 孙十一    |       4 |
|  3 |        2 | 王五      |       1 |
|  4 |        2 | 赵六      |       2 |
|  7 |        2 | 吴九      |       3 |
|  5 |        3 | 钱七      |       1 |
|  8 |        3 | 郑十      |       2 |
| 10 |        3 | 李十二    |       3 |
+----+----------+-----------+---------+
10 rows in set (0.00 sec)

图片

(2)低版本MySQL中的实现

因为涉及到分组及分组后排序,因此需要引入2个变量,一个用于分组标识,一个用于组内排序标识,示例如下:

SET @row_num = 0;
SET @g_id = NULL;


SELECT
  id,
  group_id,
  c_name,
  @row_num := CASE
                  WHEN @g_id = group_id THEN @row_num + 1
                  ELSE 1
                END AS row_num,
  @g_id := group_id AS v_gid
FROM
  users
ORDER BY
  group_id, id;

运行结果如下:

+----+----------+-----------+---------+-------+
| id | group_id | c_name    | row_num | v_gid |
+----+----------+-----------+---------+-------+
|  1 |        1 | 张三      |       1 |     1 |
|  2 |        1 | 李四      |       2 |     1 |
|  6 |        1 | 周八      |       3 |     1 |
|  9 |        1 | 孙十一    |       4 |     1 |
|  3 |        2 | 王五      |       1 |     2 |
|  4 |        2 | 赵六      |       2 |     2 |
|  7 |        2 | 吴九      |       3 |     2 |
|  5 |        3 | 钱七      |       1 |     3 |
|  8 |        3 | 郑十      |       2 |     3 |
| 10 |        3 | 李十二    |       3 |     3 |
+----+----------+-----------+---------+-------+
10 rows in set, 2 warnings (0.00 sec)

图片

这样就实现了分组及排序的序号生成。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论