SQL Server ROW_NUMBER() 進階應用：不排序、分頁、分組編號與實戰案例

ROW_NUMBER() 是 SQL Server 2005+ 引入的視窗函數，依 OVER (ORDER BY ...) 指定的排序為結果集依序編號。本文探討不指定排序、分頁、分組編號 (PARTITION BY) 等進階技巧與實戰範例。

1. 不想排序：ORDER BY (SELECT 0)

ROW_NUMBER() 強制要求 OVER (ORDER BY ...)，若不在乎順序、想避免排序成本，可用常數運算式：

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROWNUM, *
FROM Product;

(SELECT 0) 為常數，最佳化器不會實際排序，直接依資料儲存順序（通常為插入順序或叢集索引順序）編號。

2. 一般排序

SELECT ROW_NUMBER() OVER (ORDER BY Oper_Date DESC) AS ROWNUM, *
FROM Product;

3. CTE 分頁查詢（NHibernate 風格）

WITH query AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROWNUM, *
    FROM Product
)
SELECT * FROM query
WHERE ROWNUM BETWEEN 5 AND 10;  -- 第 2 頁，每頁 5 筆

4. 實戰案例：訂單表 OP_Order

假設欄位：DID(訂單號)、customerID、totalPrice、insDT(下單時間)、psd(密碼/其他)。

4.1 依欄位排序編號

SELECT email, customerID,
       ROW_NUMBER() OVER (ORDER BY psd) AS rows
FROM QT_Customer;

4.2 依金額升序編號

SELECT DID, customerID, totalPrice,
       ROW_NUMBER() OVER (ORDER BY totalPrice) AS rows
FROM OP_Order;

4.3 分組編號：每位客戶訂單依金額升序編號

SELECT 
    ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY totalPrice) AS rows,
    customerID, totalPrice, DID
FROM OP_Order;

PARTITION BY customerID 將資料依客戶分組，組內依 totalPrice 排序獨立編號 1,2,3...。

4.4 統計每位客戶下單次數

WITH tabs AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY totalPrice) AS rows,
           customerID, totalPrice, DID
    FROM OP_Order
)
SELECT MAX(rows) AS '下單次數', customerID
FROM tabs
GROUP BY customerID;

4.5 找出每位客戶最低消費訂單及其為第幾單

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
);

4.6 篩選每位客戶第一筆訂單

WITH tabs AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY insDT) AS rows, *
    FROM OP_Order
)
SELECT * FROM tabs WHERE rows = 1;

5. 視窗函數執行順序提醒

OVER 子句中的 PARTITION BY、ORDER BY 執行於 WHERE、GROUP BY、ORDER BY 之後。範例：

SELECT 
    ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY insDT) AS rows,
    customerID, totalPrice, DID
FROM OP_Order
WHERE insDT > '2011-07-22';  -- 先過濾，再分組編號

6. 效能建議

• 大資料集分頁建議搭配 OFFSET/FETCH (SQL 2012+) 或索引覆蓋查詢。
• PARTITION BY 欄位若有索引，排序成本較低。
• 避免在 OVER (ORDER BY ...) 使用非確定性函數（如 NEWID()），導致結果不穩定。

靈活運用 ROW_NUMBER() 搭配 PARTITION BY、CTE，可優雅解決分頁、分組排名、去重保留第一筆等常見需求。