MySQL 中 Row_Number() 函数的使用
-
在 MySQL 中使用
ROW_NUMBER()使用ORDER BY子句 -
在 MySQL 中使用
PARTITION BY子句使用ROW_NUMBER() -
在 MySQL 中使用
PARTITION BY和ORDER BY子句使用ROW_NUMBER() -
在 MySQL 中使用会话变量复制
ROW_NUMBER() - 结论
在本教程中，我们将介绍如何在 MySQL 中使用 ROW_NUMBER() 函数。这是一种排序方法，在分区内从 1 开始分配连续的数字。需要注意的是，分区内的两行没有相同的数字。
我们还将看到 PARTITION BY 和 ORDER BY 如何影响 MySQL 结果。你必须使用 ORDER BY 子句来使用 ROW_NUMBER()，因为它是强制性的。但是 PARTITION BY 子句是可选的。
如果同时使用 PARTITION BY 和 ORDER BY 这两个子句，结果将是不确定的。在这里，我们将看到如何使用会话变量来模拟 ROW_NUMBER() 函数以获得所需的结果。
请注意 ROW_NUMBER() 在 MySQL 8.0 版之前不可用。你会看到 MySQL 8.0 版中的新功能此处。
在 MySQL 中使用 ROW_NUMBER() 使用 ORDER BY 子句
我们将只使用带有 ORDER BY 子句的 ROW_NUMBER() 函数并观察结果。让我们首先创建表并在其中填充一些数据。
示例代码：
# SQL Programming Using MySQL Version 8.27
CREATE TABLE `test_db`.`tb_student` (
STUDENT_ID INTEGER NOT NULL,
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30) NOT NULL,
GENDER VARCHAR(30) NOT NULL,
CITY_NAME VARCHAR(64) NOT NULL,
EMAIL_ADDRESS VARCHAR(64) NOT NULL,
REGISTRATION_YEAR INTEGER NOT NULL,
PRIMARY KEY (STUDENT_ID)
);
此查询将创建一个名为 tb_student 的表，你可以在 MySQL 数据库中确认该表。
使用 INSERT 查询的以下语法将六条记录插入名为 tb_student 的表中。
# SQL Programming Using MySQL Version 8.27
INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(1,'Ayush','Kumar', 'Male', 'Washington', 'akuman@yahoo.com', 2010);
然后从表中选择所有数据以使用以下查询进行查看。
# SQL Programming Using MySQL Version 8.27
SELECT * FROM test_db.tb_student
你的表将包含以下数据。你还可以检查并比较。
# SQL Programming Using MySQL Version 8.27
SELECT *,
ROW_NUMBER() OVER(ORDER BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;
执行上述查询后，你将得到以下结果。
观察上面的输出，你会看到所有的记录都显示出来了，这些记录是按注册年份排序的（见绿色框内的列）。并且 row_number 也与预期相同，从 1 开始并随着我们从 tb_student 读取所有数据而不断增加直到表的末尾。
在 MySQL 中使用 PARTITION BY 子句使用 ROW_NUMBER()
我们将只使用带有 PARTITION BY 子句的 ROW_NUMBER() 函数并观察结果。我们还将将此输出与使用 ROW_NUMBER() 和 ORDER BY 子句获得的结果进行比较。
示例代码：
# SQL Programming Using MySQL Version 8.27
SELECT *,
ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;
现在，你将得到以下结果。
查看 REGISTRATION_YEAR 列；它有 5 个分区（2010、2011、2012、2013 和 2014）。partition 2010 的表中有两行，并且行号分配正确（再次参见上面的屏幕截图）。partition 2011, 2012, 2013, 2014 只有一行；这就是为什么你可以在 row_numb 列中看到 1。
如果我们使用 PARTITION BY 子句，那么为什么名为 REGISTRATION_YEAR 的列按升序排列？因为 PARTITION BY 子句对这些分区中的数据进行排序。让我们插入另一条记录，其 REGISTRATION_YEAR 的值为 2009 并观察结果。
# SQL Programming Using MySQL Version 8.27
INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(7,'Mashal','Naaz', 'Female', 'Florida', 'mashalnaaz@gmail.com', 2009);
SELECT *,
ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;
现在，你将看到最近的记录位于顶部。
在 MySQL 中使用 PARTITION BY 和 ORDER BY 子句使用 ROW_NUMBER()
现在，我们将只使用带有 PARTITION BY 和 ORDER BY 子句的 ROW_NUMBER() 函数，看看它是否仍然提供正确的行号。
示例代码：
# SQL Programming Using MySQL Version 8.27
SELECT *,
ROW_NUMBER() OVER(PARTITION BY REGISTRATION_YEAR ORDER BY REGISTRATION_YEAR) AS row_numb
FROM test_db.tb_student;
执行上述查询后，你将看到与使用 ROW_NUMBER() 和 PARTITION BY 子句相同的输出。请参阅以下屏幕截图：
看到黄色背景的列，这是我们所期望的。在这里，我们将使用会话变量来正确分配行号。
在 MySQL 中使用会话变量复制 ROW_NUMBER()
当我们同时使用 PARTITION BY 和 ORDER BY 子句时，MySQL 不提供正确的排名功能。在这种情况下，我们使用会话变量来模拟它。会话变量是用户定义的；你可以在此处查看详细信息。
示例代码：
# SQL Programming Using MySQL Version 8.27
SET @row_numb = 0;
SELECT *,
(@row_numb:=@row_numb + 1) AS row_numb
FROM test_db.tb_student ORDER BY REGISTRATION_YEAR;
正如你在下面看到的，row_numb 从 1 开始并连续增加。
它是如何工作的？我们首先使用@前缀设置会话变量 row_numb 并使用 0 进行初始化。然后我们从表中选择数据，对其排序并打印它。 (@row_numb:=@row_numb + 1) 就像增加和更新变量的值一样。
结论
根据上述讨论，我们得出结论，虽然我们可以在 MySQL 中使用 ROW_NUMBER() 函数，如果我们有 8.0 或更高版本，但在某些情况下我们必须使用 Session Variables 进行排名目的.
