萬盛學電腦網

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

SQL 讀取不連續 ID 區間資料:ROW_NUMBER() / CTE / NOT IN 三種分頁寫法對比

ID 有缺口 (刪除導致不連續) 時,需依邏輯順序取第 30–40 筆。三種標準寫法:

方法一:ROW_NUMBER() 衍生表 (SQL Server 2005+ 標準)

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;

關鍵:OVER (ORDER BY ...) 強制排序產生連續序號。

方法二:WITH 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;

CTE 可重複引用、支援遞迴、程式碼結構更清晰。

方法三:NOT IN 雙重 TOP (舊版相容,效能較差)

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

大量資料時 NOT IN 易產生掃描,建議改用 NOT EXISTS 或方法一/二。

關鍵字:SQL、資料、ROW_NUMBER、CTE、分頁、不連續 ID

copyright © 萬盛學電腦網 all rights reserved