MySQL ROW_NUMBER Function

摘要：在本教程中，您将了解 MySQL ROW_NUMBER()函数以及如何使用它为结果集中的每一行生成序号。

MySQL ROW_NUMBER()语法

MySQL从8.0版本开始引入了ROW_NUMBER()函数。 ROW_NUMBER()是一个窗口函数或分析函数，它为结果集中的每一行分配一个序号。第一个数字以 1 开头。

请注意，如果您使用版本低于 8.0 的 MySQL，则可以使用各种技术来模拟ROW_NUMBER()函数的某些功能。

下面显示了ROW_NUMBER()函数的语法：

ROW_NUMBER() OVER (<partition_definition> <order_definition>)Code language: SQL (Structured Query Language) (sql)

分区定义

partition_definition具有以下语法：

PARTITION BY <expression>,[{,<expression>}...]Code language: SQL (Structured Query Language) (sql)

PARTITION BY子句将行分成更小的集合。该expression可以是GROUP BY子句中使用的任何有效表达式。可以使用用逗号 ( , ) 分隔的多个表达式。

PARTITION BY子句是可选的。如果省略它，则整个结果集被视为一个分区。但是，当您使用PARTITION BY子句时，每个分区也可以被视为一个窗口。

订单定义

order_definition语法如下所示：

ORDER BY <expression> [ASC|DESC],[{,<expression>}...]Code language: SQL (Structured Query Language) (sql)

ORDER BY子句的目的是设置行的顺序。此ORDER BY子句独立于查询的ORDER BY子句。

MySQL ROW_NUMBER() 函数示例

让我们使用示例数据库中的products表进行演示：

1) 为行分配序号

以下语句使用ROW_NUMBER()函数为products表中的每一行分配序号：

SELECT 
	ROW_NUMBER() OVER (
		ORDER BY productName
	) row_num,
    productName,
    msrp
FROM 
	products
ORDER BY 
	productName;Code language: SQL (Structured Query Language) (sql)

这是输出：

2）找到每组的前N行

您可以使用ROW_NUMBER()函数查找每个组的前 N ​​行，例如按销售渠道排名前三名的销售员工或按产品类别排名前五名的高性能产品。

以下语句使用ROW_NUMBER()按产品线查找库存最高的前三种产品：

WITH inventory
AS (SELECT 
       productLine,
       productName,
       quantityInStock,
       ROW_NUMBER() OVER (
          PARTITION BY productLine 
          ORDER BY quantityInStock DESC) row_num
    FROM 
       products
   )
SELECT 
   productLine,
   productName,
   quantityInStock
FROM 
   inventory
WHERE 
   row_num <= 3;Code language: SQL (Structured Query Language) (sql)

在这个例子中，

• 首先，我们使用ROW_NUMER()函数对每个产品线中所有产品的库存进行排名，方法是按产品线对所有产品进行分区，并按库存数量降序排列。因此，每个产品都会根据其库存数量分配一个排名。并且每个产品线的排名都会重置。
• 然后，我们只选择排名小于或等于三的产品。

下面显示了输出：

3）删除重复行

您可以使用ROW_NUMBER()将非唯一行转换为唯一行，然后删除重复的行。考虑以下示例。

首先，创建一个包含一些重复值的表：

CREATE TABLE t (
    id INT,
    name VARCHAR(10) NOT NULL
);

INSERT INTO t(id,name) 
VALUES(1,'A'),
      (2,'B'),
      (2,'B'),
      (3,'C'),
      (3,'C'),
      (3,'C'),
      (4,'D');Code language: SQL (Structured Query Language) (sql)

其次，使用ROW_NUMBER()函数按所有列将行划分为分区。对于每个唯一的行集，行号将重新开始。

SELECT 
    id,
    name,
    ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS row_num
FROM t;Code language: SQL (Structured Query Language) (sql)

从输出中可以看到，唯一行是行号为 1 的行。

第三，可以使用公用表表达式（CTE）返回重复行并使用删除语句来删除：

WITH dups AS (SELECT 
        id,
        name,
        ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id) AS row_num
    FROM t)

DELETE FROM t USING t JOIN dups ON t.id = dups.id
WHERE dups.row_num <> 1;Code language: SQL (Structured Query Language) (sql)

请注意，MySQL 不支持基于 CTE 的删除。因此，您必须将原始表与 CTE连接作为解决方法。

4）使用ROW_NUMBER()函数分页

由于ROW_NUMBER()为结果集中的每一行分配一个唯一的编号，因此您可以将其用于分页。

假设您需要显示一个产品列表，每页有 10 个产品。要获取第二页的产品，请使用以下查询：

SELECT *
FROM 
    (SELECT productName,
         msrp,
         row_number()
        OVER (order by msrp) AS row_num
    FROM products) t
WHERE row_num BETWEEN 11 AND 20;    Code language: SQL (Structured Query Language) (sql)

这是输出：

在本教程中，您学习了如何使用 MySQL ROW_NUMBER()函数为结果集中的每一行生成序号。