ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 是 SQL Server 2005+ 強大的視窗函數,賦予每列唯一遞增序號,支援分組與排序,廣泛用於分頁、Top-N、去重、歷史快照等場景。
ROW_NUMBER() OVER (
[PARTITION BY 分組欄位, ...]
ORDER BY 排序欄位 [ASC|DESC], ...
) AS RowNum
SELECT
Email, CustomerId,
ROW_NUMBER() OVER (ORDER BY Psd) AS Rows
FROM QT_Customer;
SELECT
DId, CustomerId, TotalPrice,
ROW_NUMBER() OVER (ORDER BY TotalPrice) AS Rows
FROM OP_Order;
SELECT
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalPrice) AS Rows,
CustomerId, TotalPrice, DId
FROM OP_Order;
結果:同一 CustomerId 內部從 1 開始遞增。
WITH Tabs AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalPrice) AS Rows,
CustomerId, TotalPrice, DId
FROM OP_Order
)
SELECT CustomerId, MAX(Rows) AS '下單次數'
FROM Tabs
GROUP BY CustomerId;
WITH Tabs AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY InsDt) AS Rows,
CustomerId, TotalPrice, DId
FROM OP_Order
)
SELECT *
FROM Tabs
WHERE TotalPrice IN (
SELECT MIN(TotalPrice) FROM Tabs GROUP BY CustomerId
);
WITH Tabs AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY InsDt) AS Rows,
*
FROM OP_Order
)
SELECT * FROM Tabs WHERE Rows = 1;
WITH Q AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum, -- 不指定排序,按物理順序
*
FROM Product
)
SELECT * FROM Q WHERE RowNum BETWEEN 5 AND 10; -- 第 2 頁,每頁 6 筆
技巧:ORDER BY (SELECT 0) 避免排序成本,保留原始順序(非確定性,僅供分頁顯示)。正式分頁建議明確 ORDER BY PK 確保穩定。
OVER 子句中的 PARTITION BY/ORDER BY 執行時機 晚於 WHERE、GROUP BY、HAVING、ORDER BY(外層)。因此:
-- 此處 WHERE 先過濾,再對剩餘列編碼
SELECT
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY InsDt) AS Rows,
CustomerId, TotalPrice, DId
FROM OP_Order
WHERE InsDt > '2011-07-22';
OFFSET ... ROWS FETCH NEXT ... ROWS ONLY:原生分頁,語法更簡潔。RANK()、DENSE_RANK():並列同名次需求。NTILE(n):分桶分析。熟練 ROW_NUMBER() 讓複雜排序、分組統計與分頁邏輯在單一查詢中優雅完成。