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() 函數為查詢結果集中的每一列分配一個連續的整數數字。