MySQL數(shù)據(jù)庫如何生成分組排序的序號(hào)
作者：懂點(diǎn)IT的耿小廚
在MySQL8.0中可以使用窗口函數(shù)來實(shí)現(xiàn)，可以參考?xì)v史文章有了這些函數(shù)，統(tǒng)計(jì)分析事半功倍進(jìn)行了解。而MySQL5.7中由于沒有這類函數(shù)，該如何實(shí)現(xiàn)呢，下面對(duì)比MySQL8.0，列舉兩種情況的實(shí)現(xiàn)。
經(jīng)常進(jìn)行數(shù)據(jù)分析的小伙伴經(jīng)常會(huì)需要生成序號(hào)或進(jìn)行數(shù)據(jù)分組排序并生成序號(hào)。在MySQL8.0中可以使用窗口函數(shù)來實(shí)現(xiàn)，可以參考?xì)v史文章有了這些函數(shù)，統(tǒng)計(jì)分析事半功倍進(jìn)行了解。而MySQL5.7中由于沒有這類函數(shù)，該如何實(shí)現(xiàn)呢，下面對(duì)比MySQL8.0，列舉兩種情況的實(shí)現(xiàn)。
1、數(shù)據(jù)準(zhǔn)備
創(chuàng)建一張演示表：
#創(chuàng)建表
CREATE TABLE users (
id INT PRIMARY KEY,
group_id INT,
c_name VARCHAR(64)
);插入演示數(shù)據(jù)：
-- 插入10行數(shù)據(jù)
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、生成序號(hào)
（1）使用窗口函數(shù)ROW_NUMBER()實(shí)現(xiàn)
在MySQL8.0中可以直接使用窗口函數(shù)ROW_NUMBER()來實(shí)現(xiàn)序號(hào)的生成，例如：
# 根據(jù)c_name字段進(jìn)行排序生成序號(hào)
SELECT
ROW_NUMBER() OVER (ORDER BY c_name) AS row_num,
id,
c_name
FROM
users;結(jié)果如下：
+---------+----+-----------+
| 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中的實(shí)現(xiàn)
因?yàn)樵贛ySQL8.0版本之前無ROW_NUMBER()窗口函數(shù)，因此需要結(jié)束變量來實(shí)現(xiàn)。具體示例如下：
SET @row_num = 0;
SELECT
(@row_num:=@row_num + 1) AS row_num,
id,
c_name
FROM
users
ORDER BY
c_name;結(jié)果如下：
+---------+----+-----------+
| 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)
注意：每次執(zhí)行前需要將@row_num重新設(shè)置為0 ，即執(zhí)行SET @row_num = 0。
3、分組后排序
（1）繼續(xù)使用窗口函數(shù)ROW_NUMBER()實(shí)現(xiàn)
在MySQL8.0中可以繼續(xù)使用窗口函數(shù)ROW_NUMBER()來實(shí)現(xiàn)分組排序的功能，例如：
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;運(yùn)行結(jié)果如下：
+----+----------+-----------+---------+
| 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中的實(shí)現(xiàn)
因?yàn)樯婕暗椒纸M及分組后排序，因此需要引入2個(gè)變量，一個(gè)用于分組標(biāo)識(shí)，一個(gè)用于組內(nèi)排序標(biāo)識(shí)，示例如下：
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;運(yùn)行結(jié)果如下：
+----+----------+-----------+---------+-------+
| 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)
這樣就實(shí)現(xiàn)了分組及排序的序號(hào)生成。
責(zé)任編輯：姜華
來源：
數(shù)據(jù)庫干貨鋪
