MySQL ROW_NUMBER 函数

---

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

---

MySQL ROW_NUMBER() 语法

MySQL ROW_NUMBER()从8.0版开始引入了功能。这ROW_NUMBER()是一个窗口函数或分析函数，它为从1开始应用的每一行分配一个序号。 以下显示了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() 函数示例

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

1）为行分配序号

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

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

这是输出：

+---------+---------------------------------------------+--------+
| row_num | productName                                 | msrp   |
+---------+---------------------------------------------+--------+
|       1 | 18th century schooner                       | 122.89 |
|       2 | 18th Century Vintage Horse Carriage         | 104.72 |
|       3 | 1900s Vintage Bi-Plane                      |  68.51 |
|       4 | 1900s Vintage Tri-Plane                     |  72.45 |
|       5 | 1903 Ford Model A                           | 136.59 |
|       6 | 1904 Buick Runabout                         |  87.77 |
|       7 | 1911 Ford Town Car                          |  60.54 |
|       8 | 1912 Ford Model T Delivery Wagon            |  88.51 |
|       9 | 1913 Ford Model T Speedster                 | 101.31 |
|      10 | 1917 Grand Touring Sedan                    | 170.00 |
|      11 | 1917 Maxwell Touring Car                    |  99.21 |
|      12 | 1926 Ford Fire Engine                       |  60.77 |
|      13 | 1928 British Royal Navy Airplane            | 109.42 |
...

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的产品。

以下显示输出：

+------------------+----------------------------------------+-----------------+
| productLine      | productName                            | quantityInStock |
+------------------+----------------------------------------+-----------------+
| Classic Cars     | 1995 Honda Civic                       |            9772 |
| Classic Cars     | 2002 Chevy Corvette                    |            9446 |
| Classic Cars     | 1976 Ford Gran Torino                  |            9127 |
| Motorcycles      | 2002 Suzuki XREO                       |            9997 |
| Motorcycles      | 1982 Ducati 996 R                      |            9241 |
| Motorcycles      | 1969 Harley Davidson Ultimate Chopper  |            7933 |
| Planes           | America West Airlines B757-200         |            9653 |
| Planes           | American Airlines: MD-11S              |            8820 |
| Planes           | ATA: B757-300                          |            7106 |
| Ships            | The USS Constitution Ship              |            7083 |
| Ships            | The Queen Mary                         |            5088 |
| Ships            | 1999 Yamaha Speed Boat                 |            4259 |
| Trains           | 1950's Chicago Surface Lines Streetcar |            8601 |
| Trains           | Collectable Wooden Train               |            6450 |
| Trains           | 1962 City of Detroit Streetcar         |            1645 |
| Trucks and Buses | 1964 Mercedes Tour Bus                 |            8258 |
| Trucks and Buses | 1957 Chevy Pickup                      |            6125 |
| Trucks and Buses | 1980鈥檚 GM Manhattan Express          |            5099 |
| Vintage Cars     | 1932 Model A Ford J-Coupe              |            9354 |
| Vintage Cars     | 1912 Ford Model T Delivery Wagon       |            9173 |
| Vintage Cars     | 1937 Lincoln Berline                   |            8693 |
+------------------+----------------------------------------+-----------------+
21 rows in set (0.03 sec)

3）删除重复的行

您可以使用ROW_NUMBER()它将非唯一行转换为唯一行，然后删除重复行。请考虑以下示例。 首先，创建一个包含一些重复值的表：

DROP TABLE IF EXISTS rowNumberDemo;
CREATE TABLE rowNumberDemo (
    id INT,
    name VARCHAR(10) NOT NULL
);
 
INSERT INTO rowNumberDemo(id,name) 
VALUES(1,'A'),
      (2,'B'),
      (3,'B'),
      (4,'C'),
      (5,'C'),
      (6,'C'),
      (7,'D');

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

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

+------+------+---------+
| id   | name | row_num |
+------+------+---------+
|    1 | A    |       1 |
|    2 | B    |       1 |
|    3 | B    |       2 |
|    4 | C    |       1 |
|    5 | C    |       2 |
|    6 | C    |       3 |
|    7 | D    |       1 |
+------+------+---------+
7 rows in set (0.02 sec)

从输出中可以看出，唯一的行是行号等于1的行。 第三，您可以使用公用表表达式（CTE）返回要删除的重复行和delete语句：

WITH dups AS (SELECT 
        id,
        name,
        ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num
    FROM rowNumberDemo)
DELETE rowNumberDemo FROM rowNumberDemo INNER JOIN dups ON rowNumberDemo.id = dups.id
WHERE dups.row_num <> 1;

+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    4 | C    |
|    7 | D    |
+------+------+
4 rows in set (0.01 sec)

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

这是输出：

+------------------------------------------+-------+---------+
| productName                              | msrp  | row_num |
+------------------------------------------+-------+---------+
| 1936 Mercedes-Benz 500K Special Roadster | 53.91 |      11 |
| 1954 Greyhound Scenicruiser              | 54.11 |      12 |
| Pont Yacht                               | 54.60 |      13 |
| 1970 Dodge Coronet                       | 57.80 |      14 |
| 1962 City of Detroit Streetcar           | 58.58 |      15 |
| 1911 Ford Town Car                       | 60.54 |      16 |
| 1936 Harley Davidson El Knucklehead      | 60.57 |      17 |
| 1926 Ford Fire Engine                    | 60.77 |      18 |
| 1971 Alpine Renault 1600s                | 61.23 |      19 |
| 1950's Chicago Surface Lines Streetcar   | 62.14 |      20 |
+------------------------------------------+-------+---------+
10 rows in set (0.02 sec)

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

---