DISTINCT 基礎語法
-- 單欄去重
SELECT DISTINCT column_name FROM table_name;
-- 多欄組合去重 (以所有指定欄位組合判斷唯一)
SELECT DISTINCT column1, column2, column3 FROM table_name;
-- 全表去重 (所有欄位組合唯一)
SELECT DISTINCT * FROM table_name;
實戰範例
範例資料:Persons 表
| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
查詢不重複城市
SELECT DISTINCT City FROM Persons;
結果
查詢不重複 姓名+城市 組合
SELECT DISTINCT LastName, City FROM Persons;
結果
| LastName | City |
| Hansen | Sandnes |
| Svendson | Sandnes |
| Pettersen | Stavanger |
DISTINCT vs GROUP BY
| 特性 | DISTINCT | GROUP BY |
| 主要用途 | 去重顯示 | 分組聚合 |
| 可用聚合函數 | ❌ | ✅ COUNT, SUM, MAX... |
| 排序行為 | 不保證順序 (通常按索引/插入順序) | MySQL 預設按分組欄位排序 |
| 效能 | 略優 (無聚合運算) | 稍高 (含分組邏輯) |
| 標準 SQL | ✅ | ✅ |
等價轉換:
SELECT DISTINCT city FROM persons;
-- 等同於
SELECT city FROM persons GROUP BY city;
進階用法
1. COUNT(DISTINCT ...) 統計不重複值數量
-- 統計有多少個不同城市
SELECT COUNT(DISTINCT City) FROM Persons; -- 結果: 2
-- 多欄組合計數
SELECT COUNT(DISTINCT LastName, City) FROM Persons; -- 結果: 3
2. DISTINCT 配合 ORDER BY
-- MySQL 允許 DISTINCT + ORDER BY 不在 SELECT 中的欄位 (非標準 SQL)
SELECT DISTINCT City FROM Persons ORDER BY LastName; -- 可能報錯或結果不確定
-- 標準寫法:子查詢或 GROUP BY
SELECT City FROM Persons GROUP BY City ORDER BY MIN(LastName);
3. DISTINCT 配合 LIMIT (取前 N 個不重複值)
SELECT DISTINCT City FROM Persons ORDER BY City LIMIT 10;
4. 子查詢中的 DISTINCT
SELECT * FROM orders
WHERE customer_id IN (SELECT DISTINCT id FROM customers WHERE city = 'Taipei');
效能分析與優化
執行計畫關鍵字
EXPLAIN SELECT DISTINCT city FROM persons;
-- type: index (全索引掃描) 或 ref/range
-- Extra: Using index for group-by (使用索引分組,無臨時表)
-- 若出現 "Using temporary" 表示需建立臨時表去重,效能較差
優化建議
- 建立索引:
INDEX idx_city (city) 讓去重走索引掃描
- 避免 SELECT DISTINCT *:全欄去重極慢,且意義不明
- 大表去重用 GROUP BY:配合
SQL_BUFFER_RESULT 或分批處理
- 近似去重:超大資料集可用
APPROX_COUNT_DISTINCT() (MySQL 8.0.13+)
常見誤區
| 誤區 | 正確認知 |
| DISTINCT 只能用於單欄 | 可用於多欄,以組合判斷唯一 |
| DISTINCT 會保持原順序 | 不保證順序,需加 ORDER BY |
| DISTINCT 可用於聚合函數參數 | 聚合函數內可用 DISTINCT:COUNT(DISTINCT col) |
| DISTINCT 會修改資料 | 純查詢操作,不修改資料 |
PHP 實作範例
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
// 取得所有不重複城市供下拉選單
$sql = "SELECT DISTINCT city FROM members WHERE city IS NOT NULL ORDER BY city";
$result = $mysqli->query($sql);
$cities = [];
while ($row = $result->fetch_assoc()) {
$cities[] = $row['city'];
}
echo json_encode($cities, JSON_UNESCAPED_UNICODE);
// ["Sandnes","Stavanger","Taipei",...]
$result->free();
$mysqli->close();
?>