ID 有缺口 (刪除導致不連續) 時,需依邏輯順序取第 30–40 筆。三種標準寫法:
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 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 可重複引用、支援遞迴、程式碼結構更清晰。
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