萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server ROW_NUMBER() 視窗函數完整攻略:分頁、分組排序、第 N 筆、最小值筆數與首筆篩選

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 是 SQL Server 2005+ 強大的視窗函數,賦予每列唯一遞增序號,支援分組與排序,廣泛用於分頁、Top-N、去重、歷史快照等場景。

語法結構

ROW_NUMBER() OVER (
    [PARTITION BY 分組欄位, ...]
    ORDER BY 排序欄位 [ASC|DESC], ...
) AS RowNum

核心範例(以 OP_Order 訂單表為例)

1. 簡單編號(不分組)

SELECT
    Email, CustomerId,
    ROW_NUMBER() OVER (ORDER BY Psd) AS Rows
FROM QT_Customer;

2. 依總價升序編號

SELECT
    DId, CustomerId, TotalPrice,
    ROW_NUMBER() OVER (ORDER BY TotalPrice) AS Rows
FROM OP_Order;

3. 分組編號:每位客戶訂單按金額排序

SELECT
    ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalPrice) AS Rows,
    CustomerId, TotalPrice, DId
FROM OP_Order;

結果:同一 CustomerId 內部從 1 開始遞增。

4. 統計每位客戶下單次數(取 MAX(Rows))

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;

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

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;

7. 分頁查詢(經典模式)

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 確保穩定。

8. 執行順序提醒

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

進階替代方案(SQL Server 2012+)

  • OFFSET ... ROWS FETCH NEXT ... ROWS ONLY:原生分頁,語法更簡潔。
  • RANK()、DENSE_RANK():並列同名次需求。
  • NTILE(n):分桶分析。

熟練 ROW_NUMBER() 讓複雜排序、分組統計與分頁邏輯在單一查詢中優雅完成。

copyright © 萬盛學電腦網 all rights reserved