SELECT DISTINCT column_list FROM table_name;
DISTINCT 作用於 整個選取清單,即依所有列出欄位的組合去重。

SELECT DISTINCT name FROM A;

SELECT DISTINCT name, id FROM A;

實際上依 name + id 組合去重。Access、SQL Server 均支援。
-- SQL Server 支援 SELECT COUNT(DISTINCT name) FROM A; -- Access 不支援 COUNT(DISTINCT col) -- 多欄統計兩者均不支援:COUNT(DISTINCT name, id) ❌
SELECT id, DISTINCT name FROM A; -- ❌ 語法錯誤,DISTINCT 必須緊接 SELECT
DISTINCT 無法直接帶出非去重欄位,需改用:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) rn FROM A) t WHERE rn = 1;SELECT name, MAX(id) FROM A GROUP BY name;| 功能 | SQL Server | Access | MySQL | PostgreSQL |
|---|---|---|---|---|
| DISTINCT 多欄 | ✅ | ✅ | ✅ | ✅ |
| COUNT(DISTINCT col) | ✅ | ❌ | ✅ | ✅ |
| COUNT(DISTINCT col1, col2) | ❌ | ❌ | ✅ | ✅ |