分頁查詢必須先釐清五個關鍵變數:
SELECT COUNT(*) FROM table 取得)(totalRecords + pageSize - 1) / pageSizebeginRow = (pageNow - 1) * pageSize + 1endRow = pageNow * pageSize
Oracle 利用 ROWNUM 偽欄位實現分頁,邏輯為「先取前 N 筆,再從中取後 M 筆」:
-- 第 1 步:產生行號
SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp) a;
-- 第 2 步:取前 endRow 筆
SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp) a WHERE ROWNUM <= 10;
-- 第 3 步:取 beginRow 到 endRow
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp) a WHERE ROWNUM <= 10
) WHERE rn >= 6;
模板化寫法(僅需修改最內層查詢):
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM emp -- 此處替換為實際查詢(可加 WHERE、ORDER BY)
) a WHERE ROWNUM <= :endRow
) WHERE rn >= :beginRow;
-- 取第 2 頁,每頁 5 筆(筆數 6-10)
SELECT TOP 5 * FROM (
SELECT TOP 10 * FROM emp ORDER BY empid ASC
) AS t ORDER BY empid DESC;
對應變數:TOP 10 → pageNow * pageSize,TOP 5 → pageSize。
SELECT TOP 5 * FROM emp WHERE empid IN (SELECT TOP 10 empid FROM emp ORDER BY empid) ORDER BY empid DESC;
SELECT * FROM emp ORDER BY empid OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; -- 第 2 頁,每頁 5 筆
此語法符合 ANSI SQL 標準,效能最佳且可讀性最高。
SELECT *,只取必要欄位減少 I/O。COUNT(*))可考慮快取或非同步執行。