Oracle SELECT 語法詳解與分頁/Top-N 查詢最佳實踐

標準 SELECT 語法結構

SELECT [ALL | DISTINCT] <列表達式1> [, <列表達式2> ...]
FROM <表1> [, <表2> ...]
[WHERE <篩選條件>]
[GROUP BY <分組表達式> [HAVING <分組條件>]]
[ORDER BY <列> [ASC | DESC]]

• 方括號 `[ ]` 表示可選子句
• `GROUP BY` 將相同值分組，`HAVING` 過濾分組
• `ORDER BY` 排序輸出

實戰練習：分數排名查詢

場景：表 hkb_test_score (score_id, score)

1. 取前 5 名

SELECT a.score_id, a.score
FROM (SELECT * FROM hkb_test_score ORDER BY score DESC) a
WHERE ROWNUM <= 5;

2. 取第 5 名 (單筆)

SELECT a.score_id, a.score
FROM (SELECT * FROM hkb_test_score ORDER BY score DESC) a
WHERE ROWNUM <= 5
MINUS
SELECT a.score_id, a.score
FROM (SELECT * FROM hkb_test_score ORDER BY score DESC) a
WHERE ROWNUM <= 4;

3. 查詢分數相同的記錄 (並列名次)

SELECT *
FROM hkb_test_score a
WHERE a.score IN (
  SELECT score FROM hkb_test_score
  GROUP BY score
  HAVING COUNT(*) = 2
);

集合運算子比較

測試資料：

hkb_test2: (a,1) (b,2) (c,3) (g,4)
hkb_test3: (a,1) (b,2) (e,3) (f,4)

完整範例：Employee 表 CRUD

-- 建表
CREATE TABLE Employee (
  ID VARCHAR2(4) PRIMARY KEY,
  First_Name VARCHAR2(10),
  Last_Name VARCHAR2(10),
  Start_Date DATE,
  End_Date DATE,
  Salary NUMBER(8,2),
  City VARCHAR2(10),
  Description VARCHAR2(15)
);

-- 插入 8 筆測試資料 (略)

-- 基本查詢
SELECT id, first_name, last_name FROM employee;

Oracle 實現 Top-N / 分頁查詢 (ROWNUM 架構)

Oracle 無 `LIMIT`/`TOP`，標準模式：內層排序 → 外層 ROWNUM 過濾。

1. 基礎 Top-N

SELECT * FROM (
  SELECT * FROM customer ORDER BY name
) WHERE ROWNUM <= 3
ORDER BY ROWNUM ASC;

結果：按 name 字母序前 3 筆 (eighth, fifth, first)。

2. Top-N 中取第 M 筆 (M ≤ N)

SELECT id, name FROM (
  SELECT ROWNUM recno, id, name FROM (
    SELECT * FROM customer ORDER BY name
  ) WHERE ROWNUM <= 3
  ORDER BY ROWNUM ASC
) WHERE recno = 2;

結果：第 2 筆 → fifth。

3. 直接取排序後第 N 筆 (最經濟)

SELECT id, name FROM (
  SELECT ROWNUM recno, id, name FROM (
    SELECT * FROM customer ORDER BY name
  ) WHERE ROWNUM <= 2
  ORDER BY ROWNUM ASC
) WHERE recno = 2;

4. 分頁：取第 M 筆起 X 筆

SELECT id, name FROM (
  SELECT ROWNUM recno, id, name FROM (
    SELECT * FROM customer ORDER BY name
  ) WHERE ROWNUM <= (M + X - 1)
  ORDER BY ROWNUM ASC
) WHERE recno BETWEEN M AND (M + X - 1);

範例：第 2 筆起 3 筆 → `WHERE ROWNUM <= 4` + `recno BETWEEN 2 AND 4`。

12c+ 簡化語法 (OFFSET/FETCH)

-- 12c 標準 SQL 分頁
SELECT * FROM customer
ORDER BY name
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY;  -- 跳過 1 筆，取 3 筆

效能建議

• 確保 `ORDER BY` 欄位有索引，避免全表排序。
• 大量資料分頁時，`ROWNUM` 子查詢法需掃描 `M+X` 筆，深分頁效能下降；考慮游標分頁或鍵值分頁 (WHERE id > last_id)。
• 統計資訊及時收集，優化器才能選擇正確執行計畫。