Oracle、DB2、MySQL 分頁查詢與 Top-N 寫法對比

Oracle 三種分頁模式

1. 分析函數 ROW_NUMBER() — 推薦

SELECT tname, tabtype FROM (
  SELECT tname, tabtype, ROW_NUMBER() OVER (ORDER BY tname) rn FROM tab
) WHERE rn BETWEEN 100 AND 150;

2. ROWNUM 偽欄 — 需雙層巢狀

SELECT tname, tabtype FROM (
  SELECT tname, tabtype, ROWNUM rn FROM tab WHERE ROWNUM <= 150
) WHERE rn >= 100;

注意：ORDER BY 在外層才生效，內層 ROWNUM 依原順序編號。

3. RANK / DENSE_RANK / ROW_NUMBER 對比

建立測試表 mynumber(id, name)，含重複 id：

SELECT id, name,
  RANK() OVER (ORDER BY id) r1,
  DENSE_RANK() OVER (ORDER BY id) r2,
  ROW_NUMBER() OVER (ORDER BY id) r3
FROM mynumber;

• 取前 10 不同 id：WHERE r1 <= 10 (RANK)
• 取前 10 列含同分：WHERE r2 <= 10 (DENSE_RANK)
• 嚴格前 10 列：WHERE r3 <= 10 (ROW_NUMBER)

DB2 寫法 (支援標準 SQL:2003)

建表與測試資料

CREATE TABLE mynumber(id INT, name VARCHAR(10));
INSERT INTO mynumber VALUES (1,'no1'), (2,'no2'), ...;

分頁查詢

-- 標準 FETCH FIRST
SELECT * FROM mynumber FETCH FIRST 10 ROWS ONLY;

-- 配合分析函數
SELECT * FROM (
  SELECT id, name,
    RANK() OVER (ORDER BY id) r1,
    DENSE_RANK() OVER (ORDER BY id) r2,
    ROW_NUMBER() OVER (ORDER BY id) r3
  FROM mynumber
) t WHERE r3 BETWEEN 5 AND 10;

三種語意對應

• 前 10 不同 id：WHERE id IN (SELECT DISTINCT id FROM mynumber FETCH FIRST 10 ROWS ONLY)
• 前 10 列含同分：WHERE r2 <= 10
• 嚴格前 10 列：WHERE r3 <= 10

MySQL 簡潔 LIMIT 語法

SELECT id FROM mytable ORDER BY update_date DESC LIMIT 0, 10;  -- 第 1-10 筆
SELECT id FROM mytable ORDER BY update_date DESC LIMIT 10, 10; -- 第 11-20 筆

對比總結

最佳實踐建議

• 新專案優先使用標準 OFFSET ... FETCH FIRST ... (DB2/PostgreSQL/Oracle 12c+)
• 需「同分並列」語意時，明確選擇 RANK 或 DENSE_RANK
• 大量資料分頁時，配合覆蓋索引避免全表掃描