SQL 分頁查詢核心技巧：跨資料庫取第 M 到 N 筆資料的標準寫法

核心問題：如何高效取得指定範圍的記錄？

在沒有 LIMIT ... OFFSET（MySQL/PostgreSQL）或 OFFSET ... FETCH（SQL:2008 標準）的資料庫中，分頁查詢需透過子查詢與排序組合實現。核心邏輯：**先按排序鍵取前 N 筆，再從中排除前 M-1 筆**。

主流資料庫實作對照表

1. SQL Server / Sybase（TOP 關鍵字）

-- 方法 A：NOT IN 子查詢（通用但效能較差）
SELECT TOP (n-m+1) *
FROM Table
WHERE id NOT IN (SELECT TOP (m-1) id FROM Table ORDER BY id)
ORDER BY id;

-- 方法 B：MAX(id) + 子查詢（利用索引求極值，效能較好）
SELECT TOP (n-m+1) *
FROM Table
WHERE id > (
    SELECT MAX(id) FROM (
        SELECT TOP (m-1) id FROM Table ORDER BY id ASC
    ) AS Temp
)
ORDER BY id ASC;

2. Oracle（ROWNUM 偽欄位）

-- 標準雙層巢狀寫法（穩定、可用索引）
SELECT * FROM (
    SELECT t.*, ROWNUM AS rn FROM (
        SELECT * FROM Table ORDER BY id
    ) t WHERE ROWNUM <= n
) WHERE rn >= m;

-- 12c+ 可用標準 OFFSET ... FETCH
SELECT * FROM Table ORDER BY id
OFFSET m-1 ROWS FETCH NEXT n-m+1 ROWS ONLY;

3. MySQL / PostgreSQL / SQLite（原生 LIMIT/OFFSET）

SELECT * FROM Table ORDER BY id LIMIT (n-m+1) OFFSET (m-1);

4. DB2

SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM Table t
) WHERE rn BETWEEN m AND n;

通用「三層反轉」分頁法（適用於有主鍵且需避免 NOT IN 的場景）

SELECT * FROM (
    SELECT TOP pageSize *
    FROM (
        SELECT TOP (pageSize * currentPage) * FROM user_table ORDER BY id ASC
    ) AS a ORDER BY id DESC
) AS b ORDER BY id ASC;

原理解析（以第 3 頁、每頁 10 筆、id 1~80 為例）：
1. 內層 TOP 30 ORDER BY id ASC → 取得 id 1~30
2. 中層 ORDER BY id DESC → 變為 30~1
3. 取 TOP 10 → 得到 30~21（即第 21~30 筆，但為降序）
4. 外層 ORDER BY id ASC → 恢復正序 21~30

效能優化關鍵點

• 必須有叢集索引或覆蓋索引支援排序欄位，避免全表掃描與排序溢位到 tempdb。
• 大偏移量（大頁碼）時，NOT IN 或 ROWNUM 子查詢會掃描大量前置列；改用 Keyset Pagination（游標式分頁）：WHERE id > last_seen_id ORDER BY id LIMIT pageSize，效能為 O(1)。
• Oracle 12c+、PostgreSQL、SQL Server 2012+ 皆支援標準 OFFSET ... FETCH，優先使用原生語法，最佳化器可產生更優計畫。