SQL ROW_NUMBER

摘要：在本教程中，您将学习如何使用 ROW_NUMBER() 为查询结果集的每一行指定顺序号。

SQL ROW_NUMBER() 函数概述

ROW_NUMBER() 是一个 窗口函数，它为查询结果集的每一行指定一个顺序整数。

下列是 ROW_NUMBER() 函数的语法说明

ROW_NUMBER() OVER (
    [PARTITION BY expr1, expr2,...]
    ORDER BY expr1 [ASC | DESC], expr2,...
)
Code language: SQL (Structured Query Language) (sql)

在此语法中，

• 首先，PARTITION BY 子句将 FROM 子句返回的结果集划分为分区。PARTITION BY 子句是可选的。如果您省略它，则整个结果集将被视为单个分区。
• 然后，ORDER BY 子句对每个分区中的行进行排序。由于 ROW_NUMBER() 是一个顺序敏感函数，因此需要使用 ORDER BY 子句。
• 最后，每个分区中的每一行都分配一个顺序整数（称为行号）。每当跨分区边界时，该行号都会被重置。

SQL ROW_NUMBER() 示例

我们将使用 示例数据库 中的 employees 和 departments 表进行演示

A) 简单 SQL ROW_NUMBER() 示例

下列语句将查找所有员工的名、姓和工资。此外，它还使用 ROW_NUMBER() 函数为每一行添加顺序整数。

SELECT 
    ROW_NUMBER() OVER (
            ORDER BY salary
    ) row_num, 
    first_name, 
    last_name, 
    salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

下图显示部分结果集

B) 将 SQL ROW_NUMBER() 用于分页

ROW_NUMBER() 函数可用于分页。例如，如果您想在一个应用程序的表格中按页面显示所有员工，每个页面有十条记录。

• 首先，使用 ROW_NUMBER() 函数为每一行分配一个顺序整数。
• 其次，按请求的页面筛选行。例如，第一页有从 1 到 9 的行，第二页有从 11 到 20 的行，依此类推。

下列语句将返回第二页的记录，每页有十条记录。

-- pagination get page #2

SELECT * FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY salary) row_num, 
        first_name, 
        last_name, 
        salary
    FROM
        employees
    ) t    
WHERE
    row_num > 10 AND row_num <=20;
Code language: SQL (Structured Query Language) (sql)

下列显示输出

如果您想使用通用表表达式 (CTE) 而不是子查询，请使用下列查询

WITH t AS(
    SELECT 
        ROW_NUMBER() OVER (
            ORDER BY salary
        ) row_num, 
        first_name, 
        last_name, 
        salary
    FROM
        employees
)
SELECT 
    * 
FROM 
    t
WHERE 
    row_num > 10 AND 
    row_num <=20;
Code language: SQL (Structured Query Language) (sql)

C) 使用 SQL ROW_NUMBER() 找出每组中的第 n 个最高值

下列示例向您展示如何找出其所在部门中薪资最高的员工

-- find the highest salary per department
SELECT 
    department_name,
    first_name,
    last_name,
    salary
FROM 
    (
        SELECT 
            department_name,
            `ROW_NUMBER()` OVER (
                PARTITION BY department_name
                ORDER BY salary DESC) row_num, 
            first_name, 
            last_name, 
            salary
        FROM 
            employees e
            INNER JOIN departments d 
                ON d.department_id = e.department_id
    ) t
WHERE 
    row_num = 1;
Code language: SQL (Structured Query Language) (sql)

在 子查询 中

• 首先，PARTITION BY 子句将员工按部门分配。
• 其次，ORDER BY 子句按降序对每个部门中的员工按工资进行排序。
• 最后，ROW_NUMBER() 为每一行分配一个顺序整数。当部门发生变化时，该函数将重置该数字。

下列显示子查询的结果集

在外查询中，我们只选择了 row_num 值为 1 的员工行。

以下是整个查询的输出

如果您将 WHERE 子句中的谓词从 1 更改为 2、3，依此类推，您将获得薪资排第二、第三的员工，依此类推。

在本教程中，您学习了如何使用 SQL ROW_NUMBER() 函数为查询结果集的每一行指定一个顺序整数。