迹忆客 专注技术分享

在本教程中，我们将介绍如何在 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 进行排名目的.