MySQL row_number，如何模拟它

摘要：在本教程中，您将学习如何在 MySQL 中模拟row_number()函数。我们将向您展示如何向结果集中的每一行或每组行添加连续整数。

请注意，MySQL 从 8.0 版本开始支持ROW_NUMBER() 。如果您使用 MySQL 8.0 或更高版本，请查看ROW_NUMBER()函数。否则，您可以继续学习本教程以了解如何模拟ROW_NUMBER()函数。

`ROW_NUMBER()`函数简介

ROW_NUMBER()是一个窗口函数，它返回每行的序号，第一行从 1 开始。

在 8.0 版本之前，MySQL 不支持像Microsoft SQL Server 、 Oracle或PostgreSQL那样的ROW_NUMBER()函数。幸运的是，MySQL 提供了可用于模拟ROW_NUMBER()函数的会话变量。

MySQL `ROW_NUMBER` – 为每行添加行号

要模拟ROW_NUMBER()函数，您必须在查询中使用会话变量。

以下语句从employees表中返回 5 名员工，并为每行添加行号（从 1 开始）。

SET @row_number = 0; 
SELECT 
    (@row_number:=@row_number + 1) AS num, 
    firstName, 
    lastName
FROM
    employees
ORDER BY firstName, lastName    
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

在这个例子中：

首先，定义一个名为@row_number的变量，并将其值设置为0。@ @row_number是一个以@前缀表示的会话变量。
然后，从表employees中选择数据，并将每行的@row_number变量的值增加1。我们使用LIMIT子句将返回的行数限制为 5。

另一种技术是使用会话变量作为派生表并将其与主表交叉连接。请参阅以下查询：

SELECT 
    (@row_number:=@row_number + 1) AS num, 
    firstName, 
    lastName
FROM
    employees,
    (SELECT @row_number:=0) AS t
ORDER BY 
    firstName, 
    lastName    
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

请注意，派生表必须有自己的别名才能使查询在语法上正确。

MySQL `ROW_NUMBER` – 向每个组添加行号

ROW_NUMBER() OVER(PARITION BY ... )功能怎么样？例如，如果您想向每个组添加行号，并且为每个新组重置行号，该怎么办？

让我们看一下示例数据库中的payments表：

SELECT
    customerNumber, 
    paymentDate, 
    amount
FROM
    payments
ORDER BY 
   customerNumber;Code language: SQL (Structured Query Language) (sql)

假设您要为每个客户添加一个行号，并且只要客户编号发生变化，行号就会重置。

为此，您必须使用两个会话变量，一个用于行号，另一个用于存储旧客户编号，以便将其与当前客户编号进行比较，如以下查询所示：

set @row_number := 0;

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber 
			THEN @row_number + 1
        ELSE 1
    END AS num,
    @customer_no:=customerNumber customerNumber,
    paymentDate,
    amount
FROM
    payments
ORDER BY customerNumber;Code language: SQL (Structured Query Language) (sql)

在此示例中，我们在查询中使用CASE表达式。如果客户编号保持不变，我们增加@row_number变量，否则，我们将其重置为 1。

此查询使用派生表和交叉联接来生成相同的结果。

SELECT 
    @row_number:=CASE
        WHEN @customer_no = customerNumber 
          THEN 
              @row_number + 1
          ELSE 
               1
        END AS num,
    @customer_no:=customerNumber CustomerNumber,
    paymentDate,
    amount
FROM
    payments,
    (SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY 
    customerNumber;Code language: SQL (Structured Query Language) (sql)

在本教程中，您学习了两种在 MySQL 中模拟row_number窗口函数的方法。

本教程有帮助吗？

ROW_NUMBER()函数简介

MySQL ROW_NUMBER – 为每行添加行号

MySQL ROW_NUMBER – 向每个组添加行号

`ROW_NUMBER()`函数简介

MySQL `ROW_NUMBER` – 为每行添加行号

MySQL `ROW_NUMBER` – 向每个组添加行号