程式猴

 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;