简介：在本教程中，您将学习MySQL ROW_NUMBER()函数以及如何使用它为结果集中的每一行生成一个序列号。

MySQL ROW_NUMBER()语法

从8.0版开始，MySQL引入了ROW_NUMBER()函数。
ROW_NUMBER()是一个窗口函数或分析函数，它向其应用的每一行分配一个顺序号，从1开始。

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

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

ROW_NUMBER() OVER (<partition_definition> <order_definition>)

partition_definition

partition_definition具有以下语法：

PARTITION BY <expression>,[{,<expression>}...]

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

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

order_definition

order_definition语法如下所示：

ORDER BY <expression> [ASC|DESC],[{,<expression>}...]

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

MySQL ROW_NUMBER()函数示例

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

1)给行分配序号

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

SELECT 
	ROW_NUMBER() OVER (
		ORDER BY productName
	) row_num,
    productName,
    msrp
FROM 
	products
ORDER BY 
	productName;

这是输出：

2)查找每个组的前N行

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

以下语句查找每个产品系列中库存量最高的前三个产品：

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;

在这个例子中

• 首先，我们使用ROW_NUMER()函数对每个产品系列中所有产品的库存进行排名，方法是按产品系列划分所有产品，并按库存数量从高到低的顺序对其进行排序。
  结果，将根据每种产品的库存数量为其分配等级。
  并为每个产品线重置等级。

• 然后，我们仅选择排名小于或等于3的产品。

以下显示输出：

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');

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

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

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

第三，您可以使用公用表表达式(CTE)返回重复的行并使用delete语句删除：

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;

请注意，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;

这是输出：

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