分頁通用算法變數
| 變數 | 含義 | 來源 |
|---|
pageSize | 每頁筆數 | 使用者輸入 / 系統配置 |
pageNow | 當前頁碼 (1-based) | 使用者輸入 |
totalRecords | 總筆數 | `SELECT COUNT(*) FROM table` |
totalPages | 總頁數 | `CEILING(totalRecords / pageSize)` |
beginRow | 起始行號 | `(pageNow-1)*pageSize + 1` |
endRow | 結束行號 | `pageNow*pageSize` |
Oracle 三步驟 ROWNUM 模板 (經典、全版本相容)
-- 以 EMP 表為例，每頁 5 筆，查第 2 頁 (第 6-10 筆)
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM emp ORDER BY empno -- 1. 內層：基礎排序查詢
) a
WHERE ROWNUM <= 10 -- 2. 中層：截取前 endRow 筆
) WHERE rn >= 6; -- 3. 外層：過濾 beginRow 之後
參數化模板
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM ${table} ${where} ORDER BY ${orderCol}
) a
WHERE ROWNUM <= :endRow
) WHERE rn >= :beginRow;
- 只需修改**最內層**查詢 (欄位、條件、排序)，外層邏輯固定。
- Oracle 12c+ 可直接用 `OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY`。
SQL Server 三大流派
流派一：巢狀 TOP + 反向排序 (SQL 2000+ 通用)
SELECT TOP (@pageSize) *
FROM (
SELECT TOP (@pageSize * @pageNow) *
FROM emp
ORDER BY empid ASC
) t
ORDER BY empid DESC;
- 內層 `ASC` 取前 N 筆，外層 `DESC` 取最後 pageSize 筆，再隱含反轉順序 (或外層再包一層 `ASC`)。
- 參數化：`@pageSize`、 `@pageNow`。
流派二：TOP + IN 子查詢
SELECT TOP (@pageSize) *
FROM emp
WHERE empid IN (
SELECT TOP (@pageSize * @pageNow) empid
FROM emp
ORDER BY empid
)
ORDER BY empid DESC;
流派三：ROW_NUMBER() / OFFSET-FETCH (現代標準)
-- SQL 2005+ ROW_NUMBER()
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY empid) AS RN FROM emp
)
SELECT * FROM CTE WHERE RN BETWEEN (@pageNow-1)*@pageSize+1 AND @pageNow*@pageSize;
-- SQL 2012+ OFFSET-FETCH (最簡潔)
SELECT * FROM emp
ORDER BY empid
OFFSET (@pageNow-1)*@pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;
效能與選型建議
| 資料庫 | 推薦寫法 | 版本要求 | 備註 |
|---|
| Oracle | `OFFSET-FETCH` | 12c+ | 標準 SQL，最優 |
| Oracle | 三層 ROWNUM | All | 舊版唯一選擇 |
| SQL Server | `OFFSET-FETCH` | 2012+ | 原生、簡潔、效能最佳 |
| SQL Server | `ROW_NUMBER()` CTE | 2005+ | 舊版標準 |
| SQL Server | 巢狀 TOP | 2000+ | 極舊版備用 |
深分頁優化 (共通)
- 延遲關聯：內層只查 PK，外層 `JOIN` 回表。
- 游標式分頁：前端傳 `last_id`，`WHERE id > @last_id ORDER BY id LIMIT @pageSize` (需唯一遞增鍵)。
- 覆蓋索引：`INCLUDE` 所有 `SELECT` 欄位，避免書籤查找。
總結
**分頁核心變數六個**，Oracle 用 `ROWNUM` 三層包裹或 `OFFSET-FETCH`，SQL Server 用 `OFFSET-FETCH` / `ROW_NUMBER()`。深分頁**延遲關聯**通吃兩庫。新專案**強制使用標準 `OFFSET-FETCH`**，舊版才退而求其次。
