MySQL 中 Row_Number() 函数的使用

Mehvish Ashiq 2024年2月15日
  1. 在 MySQL 中使用 ROW_NUMBER() 使用 ORDER BY 子句
  2. 在 MySQL 中使用 PARTITION BY 子句使用 ROW_NUMBER()
  3. 在 MySQL 中使用 PARTITION BYORDER BY 子句使用 ROW_NUMBER()
  4. 在 MySQL 中使用会话变量复制 ROW_NUMBER()
  5. 结论
MySQL 中 Row_Number() 函数的使用

在本教程中,我们将介绍如何在 MySQL 中使用 ROW_NUMBER() 函数。这是一种排序方法,在分区内从 1 开始分配连续的数字。需要注意的是,分区内的两行没有相同的数字。

我们还将看到 PARTITION BYORDER BY 如何影响 MySQL 结果。你必须使用 ORDER BY 子句来使用 ROW_NUMBER(),因为它是强制性的。但是 PARTITION BY 子句是可选的。

如果同时使用 PARTITION BYORDER 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 数据库中确认该表。

mysql 中的 row_number - 表创建

使用 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

你的表将包含以下数据。你还可以检查并比较。

mysql 中的 row_number - 填充表

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(ORDER BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

执行上述查询后,你将得到以下结果。

mysql 中的 row_number - 带有 order by 子句的 row_number

观察上面的输出,你会看到所有的记录都显示出来了,这些记录是按注册年份排序的(见绿色框内的列)。并且 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;

现在,你将得到以下结果。

mysql 中的 row_number - 带有 partition by 子句的 row_number

查看 REGISTRATION_YEAR 列;它有 5 个分区(20102011201220132014)。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 中的 row_number - 带有 partition by 子句的 row_number

在 MySQL 中使用 PARTITION BYORDER BY 子句使用 ROW_NUMBER()

现在,我们将只使用带有 PARTITION BYORDER 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 - 带有两个子句的 row_number

看到黄色背景的列,这是我们所期望的。在这里,我们将使用会话变量来正确分配行号。

在 MySQL 中使用会话变量复制 ROW_NUMBER()

当我们同时使用 PARTITION BYORDER 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 开始并连续增加。

mysql 中的 row_number - 会话变量

它是如何工作的?我们首先使用@前缀设置会话变量 row_numb 并使用 0 进行初始化。然后我们从表中选择数据,对其排序并打印它。 (@row_numb:=@row_numb + 1) 就像增加和更新变量的值一样。

结论

根据上述讨论,我们得出结论,虽然我们可以在 MySQL 中使用 ROW_NUMBER() 函数,如果我们有 8.0 或更高版本,但在某些情况下我们必须使用 Session Variables 进行排名目的.

作者: Mehvish Ashiq
Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook