SQL Server ROW_NUMBER() OVER() 為查詢結果快速添加行號

ROW_NUMBER() 簡介

自 SQL Server 2005 起，ROW_NUMBER() 視窗函數可為查詢結果集動態生成連續行號 (1, 2, 3...)，完美替代舊版自連接、游標、臨時表等笨重寫法。

基本語法

ROW_NUMBER() OVER (ORDER BY 排序欄位 [ASC|DESC]) AS 别名

實戰範例

假設資料表 Users：

ID  NAME
1   aa
3   bb
4   cc

查詢並添加行號

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, *
FROM Users;

結果

RowNum  ID  NAME
1       1   aa
2       3   bb
3       4   cc

進階用法

1. 分組內排序 (PARTITION BY)

SELECT
    Department,
    Name,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank
FROM Employees;

依部門分組，薪資高到低排名。

2. 分頁查詢核心

DECLARE @PageSize INT = 10, @PageIndex INT = 3; -- 第 3 頁，每頁 10 筆

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RN
    FROM Articles
) AS T
WHERE RN BETWEEN (@PageIndex-1)*@PageSize + 1 AND @PageIndex*@PageSize;

3. 去重保留首筆

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CreateDate) AS RN
    FROM Subscribers
)
DELETE FROM CTE WHERE RN > 1;

效能提示

• ORDER BY 欄位若有索引，排序成本極低。
• 避免在海量資料無索引欄位上使用，會觸發大型排序運算子 (Sort Operator)。
• 配合 TOP / OFFSET-FETCH (SQL 2012+) 可進一步優化分頁。

總結

ROW_NUMBER() OVER() 是 SQL Server 產生行號的標準、高效、可讀性最佳方案，掌握其 PARTITION BY、ORDER BY 組合，即可輕鬆解決排名、分頁、去重等高頻需求。