轻松上手,快乐学习!

MySQL 教程

MySQL 首页MySQL 介绍MySQL 安装MySQL 实例库下载MySQL 实例库导入MySQL SELECTMySQL DISTINCTMySQL ORDER BYMySQL WHEREMySQL ANDMySQL ORMySQL INMySQL BETWEENMySQL LIKEMySQL LIMITMySQL IS NULLMySQL 别名MySQL JOINSMySQL INNER JOINMySQL LEFT JOINMySQL RIGHT JOINMySQL CROSS JOINMySQL SELF JOINMySQL GROUP BYMySQL HAVINGMySQL ROLLUPMySQL 子查询MySQL 派生表MySQL EXISTSMySQL CTEMySQL 递归 CTEMySQL UNIONMySQL INTERSECTMySQL MINUSMySQL INSERTMySQL INSERT INTO SELECTMySQL INSERT IGNOREMySQL UPDATEMySQL UPDATE JOINMySQL DELETEMySQL ON DELETE CASCADEMySQL DELETE JOINMySQL REPLACEMySQL PREPAREMySQL 事务MySQL 表锁定MySQL USEMySQL 数据库管理MySQL CREATE DATABASEMySQL DROP DATABASEMySQL 存储引擎MySQL CREATE TABLEMySQL 序列MySQL ALTER TABLEMySQL RENAME TABLEMySQL DROP COLUMNMySQL ADD COLUMNMySQL DROP TABLEMySQL 临时表MySQL TRUNCATE TABLEMySQL 数据类型MySQL NOT NULLMySQL Primary KeyMySQL Foreign KeyMySQL UNIQUEMySQL CHECKMySQL 字符集MySQL 排序规则MySQL 导入 CSVMySQL 导出 CSVMySQL 自然排序MySQL 基础

MySQL 存储过程

MySQL 存储过程介绍MySQL 存储过程实例MySQL 存储过程变量MySQL 存储过程参数MySQL 存储过程返回多值MySQL IF 语句MySQL CASE 语句MySQL IF CASE 选择MySQL 存储过程循环MySQL 存储过程游标MySQL 存储过程列表MySQL 存储过程异常处理MySQL SIGNAL 和 RESIGNALMySQL 存储函数

MySQL 视图

MySQL 视图教程SQL 视图介绍MySQL 视图介绍MySQL 创建视图MySQL 可更新视图WITH CHECK OPTIONLOCAL&CASCADEDMySQL 视图管理

MySQL 触发器

MySQL 触发器SQL 触发器MySQL 触发器介绍MySQL 触发器的创建MySQL 创建多个触发器MySQL 触发器管理MySQL 计划事件MySQL事件修改

MySQL 索引

MySQL 索引MySQL 索引创建MySQL 索引删除MySQL 显示索引MySQL 唯一索引MySQL 前缀索引MySQL 隐形索引MySQL 降序索引MySQL 复合索引MySQL 聚集索引MySQL 索引基数MySQL USE INDEXMySQL 强制索引

MySQL 管理

MySQL 管理MySQL访问控制系统MySQL 用户创建MySQL 用户密码MySQL 权限授予MySQL 权限撤销MySQL 角色MySQL 删除用户MySQL 表维护mysqldump 备份工具MySQL 数据库列表MySQL 表列表MySQL 表字段列表MySQL 用户列表MySQL 进程列表MySQL 列生成比较MySQL中同一表中的连续行

MySQL 全文搜索

MySQL 全文搜索MySQL 全文搜索介绍FULLTEXT索引MySQL 自然语言全文搜索MySQL 布尔全文搜索MySQL查询扩展MySQL ngram

MySQL 高级

MySQL 函数MySQL 窗口函数

MySQL 技巧

MySQL 查找重复数据MySQL 删除重复数据MySQL UUIDMySQL 表的复制MySQL 复制库MySQL 变量MySQL SELECT INTO 变量MySQL 表的存储引擎MySQL 使用正则查询MySQL 添加序号MySQL 随机查询MySQL 查询第 N 高记录MySQL 重置自增值MySQL VS MariaDBMySQL 间隔值MySQL 获取当天日期MySQL NULL 映射MySQL 注释理解MySQL EXPLAINMySQL 技巧介绍MySQL COUNTMySQL 数据分层MySQL 两表比较Mysql Like 优化

MySQL 窗口函数

MySQL CUME_DIST() 函数MySQL DENSE_RANK() 函数MySQL FIRST_VALUE 函数MySQL LAG() 函数MySQL LAST_VALUE() 函数MySQL LEAD 函数MySQL NTH_VALUE 函数MySQL NTILE 函数MySQL PERCENT_RANK 函数MySQL RANK 函数MySQL ROW_NUMBER 函数

MySQL 应用

MySQL应用程序编程接口PHP MySQL教程PHP 数据库连接

MySQL 优化

mysqld 内存持续变高


MySQL ROW_NUMBER 函数


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


MySQL ROW_NUMBER() 语法

MySQL ROW_NUMBER()从8.0版开始引入了功能。这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() 函数示例

让我们使用示例数据库中的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()函数为结果集中的每一行生成序列号。