傳統 NOT IN 分頁法
USE test;
-- 建立測試表
CREATE TABLE test (
id INT PRIMARY KEY IDENTITY,
name VARCHAR(20) NOT NULL
);
-- 插入 10 筆測試資料
INSERT INTO test(name) VALUES('test1'),('test2'),('test3'),('test4'),('test5'),
('test6'),('test7'),('test8'),('test9'),('test10');
-- 第 1 頁 (每頁 10 筆，跳過 0 筆)
SELECT TOP 10 id, name
FROM test
WHERE id NOT IN (SELECT TOP (10 * 0) id FROM test ORDER BY id)
ORDER BY id;
-- 第 2 頁 (跳過 10 筆)
SELECT TOP 10 id, name
FROM test
WHERE id NOT IN (SELECT TOP (10 * 1) id FROM test ORDER BY id)
ORDER BY id;
通用分頁範本
DECLARE @PageSize INT = 10;
DECLARE @PageNow INT = 2; -- 第 2 頁
SELECT TOP (@PageSize) id, name
FROM test
WHERE id NOT IN (
SELECT TOP (@PageSize * (@PageNow - 1)) id
FROM test
ORDER BY id
)
ORDER BY id;
效能考量與現代替代方案
| 方法 | 適用版本 | 效能 | 備註 |
|---|
| NOT IN + TOP | 所有版本 | 大資料量效能差 | 子查詢需排序全表 |
| ROW_NUMBER() OVER() | SQL 2005+ | 良好 | 單次掃描、支援複雜排序 |
| OFFSET FETCH | SQL 2012+ | 最佳 | ANSI 標準、最佳化器支援最好 |
| Keyset Pagination | 所有版本 | 極佳 | 適合 API 分頁、需唯一排序鍵 |
ROW_NUMBER() 範例 (SQL 2005+ 推薦)
WITH Paged AS (
SELECT id, name,
ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM test
)
SELECT id, name
FROM Paged
WHERE rn BETWEEN (@PageNow - 1) * @PageSize + 1 AND @PageNow * @PageSize
ORDER BY rn;
OFFSET FETCH 範例 (SQL 2012+ 標準寫法)
SELECT id, name
FROM test
ORDER BY id
OFFSET (@PageNow - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
