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 子句將結果集劃分為分割槽。PARTITION BY 子句是可選的。如果省略它，ROW_NUMBER 函式會將整個結果集視為單個分割槽。
• ORDER BY 子句對每個分割槽中的行進行排序。由於 ROW_NUMBER() 是一個對順序敏感的函式，因此 ORDER BY 子句是強制性的。
• ROW_NUMBER() 函式為每個分割槽內的每一行分配一個連續的整數序號。當跨越分割槽邊界時，它會重置行號。

SQL ROW_NUMBER() 函式示例 #

我們將使用示例資料庫中的 employees 和 departments 表進行演示。

基本的 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)

試一試

下圖顯示了部分結果集：

使用 SQL ROW_NUMBER() 函式進行分頁 #

ROW_NUMBER() 函式對於分頁非常有用。

例如，如果您想在應用程式的表格中按頁顯示所有員工，每頁有十條記錄。

• 首先，使用 ROW_NUMBER() 函式為每一行分配一個連續的整數序號。
• 其次，按請求的頁面篩選行。第一頁包括從 1 到 10 的行，第二頁包括從 11 到 20 的行，依此類推。

以下語句返回第二頁的行：

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)

查詢每個分組中的第 n 高值 #

以下示例向您展示如何找出在各自部門中薪水最高的員工。

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() 函式為同一分割槽內的每一行分配一個連續的整數序號。

資料庫 #

• PostgreSQL ROW_NUMBER 函式
• Oracle ROW_NUMBER 函式
• SQL Server ROW_NUMBER 函式
• MySQL ROW_NUMBER 函式
• SQLite ROW_NUMBER 函式