select row_number()

<think>我们正在讨论SQL中的ROW_NUMBER()函数。根据引用内容，ROW_NUMBER()是一个窗口函数，用于为结果集的每一行分配一个唯一的连续整数编号，编号基于指定的排序顺序，并在每个分区（如果使用了PARTITION BY）内独立计算。 用户意图是了解ROW_NUMBER()函数的功能和用法。因此，我们需要解释其作用并提供使用示例。 ### 回答结构 1. **ROW_NUMBER()函数的作用**：简要说明函数的功能。 2. **语法**：给出基本语法。 3. **使用示例**：提供几个典型示例，包括： - 基本使用（无分区） - 使用PARTITION BY进行分组 - 实际应用场景（如分页、删除重复数据等） ### 参考引用 - 引用[1]提到：ROW_NUMBER函数用于为每一行分配一个序号。 - 引用[2]：ROW_NUMBER()是窗口函数，用于对与当前行相关的一组行进行计算。 - 引用[3]：详细说明了ROW_NUMBER()的作用：为每一行分配唯一的连续整数编号，基于排序顺序，每个分区内独立计算。 - 引用[4]：提供了一个具体示例，按name分组并取每组id最小的一条数据。 ### 回答内容 #### ROW_NUMBER()函数的作用 ROW_NUMBER()函数是SQL中的窗口函数，它为查询结果集中的每一行分配一个唯一的连续整数编号。编号的生成基于ORDER BY子句指定的排序顺序，并且如果使用了PARTITION BY子句，则会在每个分区内重新开始编号。这使得ROW_NUMBER()非常适用于排名、分页和删除重复数据等场景[^3]。 #### 基本语法 ```sql ROW_NUMBER() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... ) ``` - `PARTITION BY`：可选，用于将结果集划分为多个分区，函数在每个分区内独立编号。 - `ORDER BY`：指定排序顺序，编号根据此顺序生成。 #### 使用示例 假设我们有一个`test_user`表，包含`id`, `name`, `age`等字段。 **示例1：基本使用（无分区）** 为所有用户按年龄升序分配序号： ```sql SELECT id, name, age, ROW_NUMBER() OVER (ORDER BY age ASC) AS row_num FROM test_user; ``` 结果中，每一行会有一个从1开始的连续编号，按年龄从小到大排列。 **示例2：使用PARTITION BY分组** 按`name`分组，组内按`id`升序编号： ```sql SELECT id, name, age, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id ASC) AS row_num FROM test_user; ``` 这样，每个`name`组内的行都会独立编号（从1开始），按`id`升序排列。 **示例3：取每组的第一条记录（删除重复数据）** 如果我们想取每个`name`组中`id`最小的那条记录（即每个`name`只显示一条）： ```sql SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id ASC) AS row_id FROM test_user ) AS a WHERE a.row_id = 1; ``` 此查询先为每个`name`组内的行按`id`升序编号（最小的`id`编号为1），然后外层查询只取编号为1的行[^4]。 **示例4：分页查询** ROW_NUMBER()常用于分页。例如，按年龄排序后取第11至20条记录： ```sql SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY age ASC) AS row_num FROM test_user ) AS a WHERE a.row_num BETWEEN 11 AND 20; ``` ### 注意事项 - ROW_NUMBER()分配的编号是唯一的，且连续（相邻行编号差1）。 - 当排序字段值相同时，ROW_NUMBER()会任意分配不同的编号（与RANK()和DENSE_RANK()不同，它们会允许并列排名）。 - 使用PARTITION BY时，每个分区内编号都从1开始。 §§相关问题§§ 1. ROW_NUMBER()与RANK()、DENSE_RANK()窗口函数有什么区别？ 2. 如何在更新数据时使用ROW_NUMBER()函数？ 3. ROW_NUMBER()函数在哪些数据库系统中可用？</think>### ROW_NUMBER() 函数在 SQL 中的作用及使用详解 #### 1. **核心作用** ROW_NUMBER() 是 SQL 中的**窗口函数**（Window Function），主要功能是为查询结果集的每一行分配一个**唯一的连续整数编号**。编号生成规则： - 基于 `ORDER BY` 指定的排序顺序 - 若使用 `PARTITION BY`，则**在每个分组内独立编号**（分组内重新从1开始） - 编号严格连续且无重复（如：1,2,3,...）[^3] #### 2. **基本语法** ```sql ROW_NUMBER() OVER ( [PARTITION BY column1, column2...] ORDER BY sort_column [ASC|DESC] ) AS row_num ``` - `PARTITION BY`：可选，定义分组列 - `ORDER BY`：必选，决定编号顺序 #### 3. **使用示例** **示例1：基础排序编号** 为所有员工按薪资降序编号： ```sql SELECT employee_id, name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees; ``` 结果： | employee_id | name | salary | rank | |-------------|-------|--------|------| | 103 | Alice | 9000 | 1 | | 101 | Bob | 8000 | 2 | | 102 | Carol | 7000 | 3 | **示例2：分组内编号** 按部门分组，组内按入职时间排序： ```sql SELECT department_id, employee_id, hire_date, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY hire_date ASC ) AS dept_rank FROM employees; ``` 结果（部门10的示例）： | department_id | employee_id | hire_date | dept_rank | |---------------|-------------|------------|-----------| | 10 | 1001 | 2020-01-01 | 1 | | 10 | 1003 | 2021-03-15 | 2 | **示例3：删除重复数据** 保留每个部门薪资最高的记录（实战经典场景）： ```sql DELETE FROM employees WHERE employee_id IN ( SELECT employee_id FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rn FROM employees ) AS tmp WHERE rn > 1 -- 只保留每组第1名 ); ``` 此操作会删除每个部门中薪资非最高的重复记录[^4]。 **示例4：分页查询** 获取第11~20条记录（分页场景）： ```sql SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM products ) AS paged WHERE rn BETWEEN 11 AND 20; ``` #### 4. **注意事项** - 与 `RANK()`/`DENSE_RANK()` 的区别： - `ROW_NUMBER()` 始终生成**唯一连续编号**（无并列） - `RANK()` 允许并列（如：1,1,3） - `DENSE_RANK()` 允许并列且连续（如：1,1,2） - 性能：大数据量时，确保 `ORDER BY` 列有索引 - 兼容性：支持所有主流数据库（SQL Server, PostgreSQL, MySQL 8.0+, Oracle） > 关键应用场景：数据去重、分页查询、分组取TOP N、历史记录版本标记等[^2][^3]。