SELECT [ALL | DISTINCT] 運算式1 [, 運算式2 ...]
FROM 表1 [, 表2 ...]
[WHERE 篩選條件]
[GROUP BY 分組運算式 [HAVING 分組條件]]
[ORDER BY 運算式 [ASC | DESC]];
[] 為可選子句GROUP BY 先分組,HAVING 過濾組ORDER BY 最後排序SELECT a.sore_id, a.sore
FROM (SELECT * FROM hkb_test_sore ORDER BY sore DESC) a
WHERE ROWNUM <= 5;
SELECT a.sore_id, a.sore
FROM (SELECT * FROM hkb_test_sore ORDER BY sore DESC) a
WHERE ROWNUM <= 5
MINUS
SELECT a.sore_id, a.sore
FROM (SELECT * FROM hkb_test_sore ORDER BY sore DESC) a
WHERE ROWNUM <= 4;
SELECT *
FROM hkb_test_sore a
WHERE a.sore IN (
SELECT sore FROM hkb_test_sore
GROUP BY sore HAVING COUNT(*) = 2
);
| 運算子 | 說明 | 去重 |
|---|---|---|
| UNION | 合併兩結果集 | 是 |
| UNION ALL | 合併不去重 | 否 |
| INTERSECT | 交集 | 是 |
| MINUS | 差集 (第一組減第二組) | 是 |
hkb_test2: (a,1),(b,2),(c,3),(g,4)
hkb_test3: (a,1),(b,2),(e,3),(f,4)
UNION: a1,b2,c3,e3,f4,g4 (6 rows)
UNION ALL: a1,b2,c3,g4,a1,b2,e3,f4 (8 rows)
INTERSECT: a1,b2 (2 rows)
MINUS: c3,g4 (2 rows)
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;
SELECT col1, col2, ...
FROM (
SELECT col1, col2, ...
FROM table_name
ORDER BY col1, col2 ...
)
WHERE ROWNUM <= N
ORDER BY ROWNUM ASC; -- 可選,保證順序
原理:內層排序 → 外層 ROWNUM 截取。Oracle 12c+ 可用 FETCH FIRST N ROWS ONLY 標準語法。