-- 方式 1:串聯欄位去重
SELECT DISTINCT(t.f_resume_id || t.f_resume_status),
t.f_resume_status, t.f_recruit_channel, t.f_small_channel_id
FROM css.t_resume_info t
WHERE t.f_resume_status IN (80011, 80012);
-- 方式 2:直接列出欄位 (Oracle 10g+ 支援)
SELECT DISTINCT t.f_resume_id, t.f_resume_status, t.f_recruit_channel, t.f_small_channel_id
FROM css.t_resume_info t
WHERE t.f_resume_status IN (80011, 80012);
SELECT a, b, c, MIN(d) AS d
FROM A
GROUP BY a, b, c;
配合聚合函數可保留非分組欄位的代表值。
| 場景 | 推薦 | 理由 |
|---|---|---|
| 單純去重、無聚合需求 | DISTINCT | 語義清晰、優化器可直接走索引快速全掃描 |
| 需聚合統計 (COUNT/SUM/AVG/MIN/MAX) | GROUP BY | 語義匹配、可利用 HASH/ SORT GROUP BY 優化 |
| 大數據量、高基數欄位 | DISTINCT/GROUP BY 皆需排序/雜湊 | 關注臨時表空間、PGA 設定、並行度 |
| 分頁查詢去重 | ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) | 窗口函數避免兩次掃描 |
DISTINCT 可能觸發 SORT UNIQUE,GROUP BY 觸發 HASH GROUP BY 或 SORT GROUP BY,查看執行計畫選擇較低成本者。INDEX FULL SCAN 避免排序。