您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 群組函式與子查詢完整筆記：彙總統計、HAVING 過濾、集合運算、分頁查詢與相關子查詢

編輯：Oracle 資料庫基礎

群組函式 (Aggregate Functions) 搭配 GROUP BY 進行資料彙總；子查詢將查詢巢狀化解決條件不確定問題。本文完整整理七大群組函式、GROUP BY/HAVING 語法限制、集合運算 (UNION/INTERSECT/MINUS)、分頁查詢標準寫法、相關子查詢與標量子查詢應用。

1. 七大群組函式

函式	功能	忽略 NULL	範例
AVG	平均值	是	`AVG(salary)`
COUNT	筆數	`COUNT(*)` 不忽略 / `COUNT(col)` 忽略	`COUNT(DISTINCT dept_id)`
MAX / MIN	最大/最小值	是	`MAX(hire_date)`
SUM	總和	是	`SUM(salary)`
STDDEV	標準差	是	`STDDEV(salary)`
VARIANCE	變異數	是	`VARIANCE(salary)`

COUNT 細節


COUNT(*)           -- 統計列數 (含 NULL 列)，效能最好 (若有 PK)
COUNT(1) / COUNT('X') -- 同 COUNT(*)
COUNT(col)         -- 統計 col 非 NULL 筆數
COUNT(DISTINCT col)-- 統計 col 非 NULL 不重複值數

2. GROUP BY / HAVING 語法與限制


SELECT dept_id, job_id, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY dept_id, job_id
HAVING AVG(salary) > 5000
ORDER BY avg_sal DESC;

限制規則

SELECT 清單欄位必須出現在 GROUP BY 或群組函式中。
ORDER BY 不可使用位置編號 (1,2) 或別名 (部分版本支援別名)，建議完整寫運算式。
WHERE 在分組前過濾列；HAVING 在分組後過濾群組。
若需計算 NULL 參與彙總，先 NVL(col, 0)。

3. 找出重複值


SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

4. 子查詢分類與應用

類型	位置	特點	範例
單列子查詢	WHERE/HAVING	回傳 1 行 1 列，可用 = > < 比較	`WHERE salary > (SELECT AVG(salary) FROM employees)`
多列子查詢	WHERE/HAVING	回傳多行 1 列，必須用 IN / ANY / ALL	`WHERE dept_id IN (SELECT dept_id FROM depts WHERE loc_id=1700)`
多欄子查詢	WHERE	回傳 1 行多列，配對比較	`WHERE (dept_id, job_id) IN (SELECT dept_id, job_id FROM ...)`
標量子查詢	SELECT/WHERE/ORDER BY	回傳 1 值，可當運算式用	`SELECT e.*, (SELECT d.name FROM depts d WHERE d.id=e.dept_id) AS dept_name FROM emp e`
相關子查詢	WHERE/SELECT	參考外層查詢欄位，逐列執行	`SELECT * FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept_id=e.dept_id)`
內嵌視圖	FROM	作為虛擬表參與聯結	`SELECT * FROM (SELECT dept_id, AVG(sal) avg_sal FROM emp GROUP BY dept_id) v JOIN depts d ON v.dept_id=d.dept_id`

5. 集合運算


-- 並集 (去重)
SELECT dept_id FROM employees UNION SELECT dept_id FROM departments;
-- 並集 (保留重複)
SELECT dept_id FROM employees UNION ALL SELECT dept_id FROM departments;
-- 交集
SELECT dept_id FROM employees INTERSECT SELECT dept_id FROM departments;
-- 差集 (前者有、後者無)
SELECT dept_id FROM employees MINUS SELECT dept_id FROM departments;

規則： 兩邊欄位數、型別、順序必須一致；ORDER BY 僅能放最後。

6. 分頁查詢標準寫法 (Oracle 12c 前)


-- 取第 81-100 筆 (假設 id 為 PK)
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM employees ORDER BY salary DESC
  ) a WHERE ROWNUM <= 100
) WHERE rn > 80;

-- 12c+ 簡潔語法
SELECT * FROM employees ORDER BY salary DESC
OFFSET 80 ROWS FETCH NEXT 20 ROWS ONLY;