SQL 分頁查詢三大經典寫法：ROW_NUMBER、CTE 遞歸與 TOP NOT IN 實戰對比

場景

從結果集中取出第 30~40 行（非連續區間），主要針對 SQL Server / 支援窗口函數的資料庫。

方法一：ROW_NUMBER() 窗口函數（最通用、效能佳）

SELECT row_num, *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY I_VoucherMaterialID) AS row_num
    FROM dbo.I_VoucherMaterial
) tt
WHERE row_num BETWEEN 30 AND 40;

關鍵： ROW_NUMBER() 必須搭配 OVER (ORDER BY ...) 指定排序。

方法二：CTE (Common Table Expression) 遞歸寫法

WITH OrderedOrders AS (
    SELECT SalesOrderID, OrderDate,
           ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
    FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 30 AND 40;

優勢： 結構清晰，易於維護；WITH 子句定義臨時結果集，支援遞歸查詢。

方法三：TOP + NOT IN 傳統寫法（不支援窗口函數的舊版本）

SELECT * FROM (
    SELECT TOP 40 * FROM I_VoucherMaterial ORDER BY I_VoucherMaterialID
) a
WHERE I_VoucherMaterialID NOT IN (
    SELECT TOP 30 I_VoucherMaterialID FROM I_VoucherMaterial ORDER BY I_VoucherMaterialID
);

缺點： 子查詢執行兩次排序，大數據量效能差；需確保排序欄位唯一，否則可能遺漏/重複行。

效能建議

1. 優先使用 方法一（單次掃描、優化器支援良好）。
2. 大表務必在 ORDER BY 欄位上建立索引。
3. 避免在 NOT IN 子查詢中使用可為 NULL 的欄位（會導致空結果集）。