| 資料庫 | 分頁語法 | 參數說明 |
|---|---|---|
| MySQL | SELECT ... LIMIT offset, rows | offset 從 0 起；rows 為頁大小 |
| PostgreSQL | SELECT ... LIMIT rows OFFSET offset | 同 MySQL，語序不同 |
| Oracle | 三層嵌套 ROWNUM 或 12c+ OFFSET ... FETCH | 見下文詳細範例 |
| SQL Server | 2012+ OFFSET ... FETCH；舊版 TOP + NOT IN/游標 | 見下文 |
| DB2 | SELECT ... FROM (SELECT ROW_NUMBER() OVER(...) AS rn, t.* FROM t) WHERE rn BETWEEN ? AND ? | 標準窗口函數 |
| InterBase | SELECT ... ROW start TO end | 專有語法 |
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM emp ORDER BY sal DESC
) a WHERE ROWNUM <= 40
) WHERE rn > 21;原理：內層排序 → 中層截取前 N 行 (STOPKEY 優化) → 外層過濾起始行。
優勢：中層 ROWNUM <= :max_row 觸發 COUNT STOPKEY，避免全表掃描。
SELECT * FROM emp ORDER BY sal DESC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;SELECT * FROM (
SELECT t.*, ROW_NUMBER() OVER (ORDER BY sal DESC) rn FROM emp t
) WHERE rn BETWEEN 21 AND 40;-- 基礎
SELECT * FROM t_order LIMIT 5, 10; -- 第 6-15 行
-- 百萬級深分頁優化：子查詢定位主鍵再回表
SELECT * FROM t_order
WHERE id >= (SELECT id FROM t_order ORDER BY id LIMIT 490000, 1)
LIMIT 10;| 方案 | 適用版本 | 語法 | 效率 |
|---|---|---|---|
| TOP + NOT IN | 2000+ | SELECT TOP 10 * FROM t WHERE id NOT IN (SELECT TOP 20 id FROM t ORDER BY id) ORDER BY id | 中 |
| TOP + MAX(id) | 2000+ | SELECT TOP 10 * FROM t WHERE id > (SELECT MAX(id) FROM (SELECT TOP 20 id FROM t ORDER BY id) a) ORDER BY id | 高 (需主鍵索引) |
| ROW_NUMBER() | 2005+ | SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) rn, * FROM t) a WHERE rn > 20 AND rn <= 30 | 高，標準寫法 |
| OFFSET/FETCH | 2012+ | SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY | 最佳，原生優化 |
| 游標/存儲過程 | 所有 | sp_cursoropen/fetch/close | 低，通用但資源消耗大 |
多表分頁時，加 /*+ FIRST_ROWS */ 引導優化器選擇 NESTED LOOP，避免 HASH JOIN 全量處理：
SELECT /*+ FIRST_ROWS */ * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM t1 JOIN t2 ON ... WHERE ...
) a WHERE ROWNUM <= :max
) WHERE rn > :min;-- 1. rowid 定位 (適合大表、無序)
SELECT * FROM passvehicleinfo p
WHERE rowid IN (
SELECT rid FROM (
SELECT rownum rn, rid FROM (
SELECT p.rowid rid FROM passvehicleinfo p ORDER BY p.passvehicleid DESC
) WHERE rownum < 10000
) WHERE rn > 9980
) ORDER BY p.passvehicleid ASC;
-- 2. ROW_NUMBER() 窗口函數 (10g+，可讀性佳)
SELECT * FROM (
SELECT p.*, ROW_NUMBER() OVER (ORDER BY p.passvehicleid DESC) rk
FROM passvehicleinfo p
) WHERE rk > 9980 AND rk < 10000;
-- 3. 經典 ROWNUM 三層嵌套 (最通用)
SELECT * FROM (
SELECT view1.*, ROWNUM rn FROM (
SELECT p.* FROM passvehicleinfo p ORDER BY p.passvehicleid DESC
) view1 WHERE ROWNUM < 10000
) view2 WHERE rn > 9980;
