深入探究SQL row_number()函數

一、row_number()函數的概述

SQL row_number()函數是一種用於給每一行分配唯一數字（行號）的窗口函數。它可以將查詢結果按照指定的列排序，並基於排序結果分配行號。row_number()函數返回的行號始終是正整數。語法如下：

ROW_NUMBER() OVER (ORDER BY column ASC/DESC)[,partition_column]

其中，ORDER BY關鍵字用於指定基於哪個列排序，ASC表示升序排序，DESC表示降序排序。partition_column是可選的，用於指定每個分區的依據列，如果未指定，則整個結果集都作為一個分區。

二、語法實例

下面的示例將使用在一個簡單的員工表中，此表包含員工姓名、部門、薪資等欄位。

CREATE TABLE employees (
  employee_id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees (employee_id, name, department, salary)
VALUES
  (101, 'Alice', 'Sales', 5000),
  (102, 'Bob', 'Operations', 6000),
  (103, 'Charlie', 'Sales', 4500),
  (104, 'David', 'Marketing', 8000),
  (105, 'Emma', 'Operations', 7000);

現在，我們要獲取員工表中每個部門的最高薪資，並按照薪資降序排列。可以使用以下SQL語句：

SELECT department, MAX(salary) as Max_Salary
FROM employees
GROUP BY department
ORDER BY Max_Salary DESC;

執行上述語句後，我們得到了每個部門的最高薪資。然而，我們只想知道每個部門的最高薪資排名，該怎麼辦呢？

這時，我們需要使用row_number()函數：

SELECT department, MAX(salary) as Max_Salary, 
       ROW_NUMBER() OVER (ORDER BY MAX(salary) DESC) as rank
FROM employees
GROUP BY department
ORDER BY Max_Salary DESC;

執行上述SQL語句後，我們得到了每個部門的最高薪資和相應的排名。

三、row_number()函數的常見用途

1. 分組中使用row_number()

對於上述示例中的問題，除了使用row_number()函數外，還可以使用子查詢等方法來實現。但是，當我們需要獲取排名和分組篩選結果時就需要用到row_number()函數。

例如，要獲取每個部門最高薪資排名前兩名的員工記錄，可以使用以下SQL代碼：

SELECT department, name, salary, ROW_NUMBER() OVER 
   (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
WHERE (department, salary) IN 
   (SELECT department, MAX(salary) FROM employees GROUP BY department)
AND rank < 3
ORDER BY department, rank;

上述代碼中，ROW_NUMBER()函數的PARTITION BY關鍵字用於指定分組規則，ORDER BY關鍵字指定了排序規則。

2. 分頁查詢中使用row_number()

row_number()函數常用於分頁查詢中，可以通過設置不同的排名條件，實現不同的分頁需求。

例如，要獲取員工表第2頁的數據，每頁顯示3條記錄，可以使用以下SQL代碼實現：

WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY employee_id) as row_num
    FROM employees
)
SELECT employee_id, name, department, salary
FROM CTE
WHERE row_num > 3 AND row_num <= 6
ORDER BY employee_id;

上述代碼中，CTE（公共表表達式）用於生成一個包含每個員工的行號的結果集。接下來，我們對該結果集進行過濾，獲取第2頁的員工數據。

3. 為結果集添加自定義行號

row_number()函數不僅可以為結果集添加基於列排序的行號，還可以為結果集添加自定義行號。

例如，我們需要為分組查詢結果添加自定義行號（按照最高薪資降序排列），可以使用以下SQL代碼：

SELECT RANK() OVER (ORDER BY Max_Salary DESC) as rank, 
       department, MAX(salary) as Max_Salary
FROM employees
GROUP BY department
ORDER BY Max_Salary DESC;

上述代碼中，使用RANK()函數代替row_number()函數，其餘代碼與之前示例基本相同。

四、總結

SQL row_number()函數是一種強大的窗口函數，能夠為結果集添加行號，常用於分組、分頁查詢等場景。通過本文的闡述，我們可以更加深入地了解row_number()函數的使用方法和應用場景，為我們在日常的資料庫開發工作中提供了有力的支持。