MySQL 去重核心技法：單表 DISTINCT 限制與多表 GROUP BY 實戰對比

DISTINCT 僅能返回目標欄位，無法攜帶其他欄位；多表去重需用 GROUP BY 配合聚合函數「欺騙」優化器。本文通過實戰案例，演示 DISTINCT 失效場景、GROUP BY 正確姿勢、以及 COUNT(DISTINCT) 經典技巧。

DISTINCT 的核心限制

-- 表 t (id, name)
-- 數據：(1,a) (2,b) (3,c) (4,c) (5,b)

SELECT DISTINCT name FROM t;           -- 正確：a,b,c
SELECT DISTINCT name, id FROM t;       -- 失效：需 id+name 同時相同才去重
SELECT id, DISTINCT name FROM t;       -- 語法錯誤：DISTINCT 必須在首位

結論：DISTINCT 作用於**整行選擇列表**，無法「只按某列去重、保留其他列任意值」。

經典技巧：COUNT(DISTINCT) + GROUP BY 欺騙優化器

SELECT *, COUNT(DISTINCT name) FROM t GROUP BY name;
-- 結果：id=1,name=a,count=1 / id=2,name=b,count=1 / id=3,name=c,count=1
-- 達到「按 name 去重、保留第一行 id」效果

原理：GROUP BY name 強制按 name 分組，COUNT(DISTINCT name) 恆為1（滿足 ONLY_FULL_GROUP_BY），MySQL 返回組內第一行（非確定性，但實際常為首行）。

GROUP BY 實戰：多表 JOIN 去重

場景：term_node tn JOIN node n JOIN node_images ni，同一 nid 因多圖產生多行，需按 nid 去重保留任意一行。

錯誤嘗試：DISTINCT

SELECT DISTINCT n.nid, tn.tid, n.title, n.created, ni.thumbpath
FROM term_node tn
JOIN node n ON n.nid = tn.nid
JOIN node_images ni ON ni.nid = n.nid
WHERE tn.tid IN (...)
ORDER BY n.nid DESC;
-- 失效：thumbpath 不同導致 DISTINCT 不生效

正確方案：GROUP BY nid

SELECT n.nid, tn.tid, n.title, n.created, ni.thumbpath
FROM term_node tn
JOIN node n ON n.nid = tn.nid
JOIN node_images ni ON ni.nid = n.nid
WHERE tn.tid IN (...)
GROUP BY n.nid
ORDER BY n.nid DESC;
-- 成功：按 nid 分組，每組返回一行（thumbpath 取組內任意值）

嚴格模式（ONLY_FULL_GROUP_BY）兼容寫法

-- 5.7+ 預設開啟，需顯式聚合非分組欄位
SELECT 
  n.nid,
  MAX(tn.tid) AS tid,
  MAX(n.title) AS title,
  MAX(n.created) AS created,
  MAX(ni.thumbpath) AS thumbpath
FROM term_node tn
JOIN node n ON n.nid = tn.nid
JOIN node_images ni ON ni.nid = n.nid
WHERE tn.tid IN (...)
GROUP BY n.nid
ORDER BY n.nid DESC;

現代最佳實踐：ROW_NUMBER() 窗口函數 (MySQL 8.0+)

SELECT nid, tid, title, created, thumbpath FROM (
  SELECT 
    n.nid, tn.tid, n.title, n.created, ni.thumbpath,
    ROW_NUMBER() OVER (PARTITION BY n.nid ORDER BY ni.id) AS rn
  FROM term_node tn
  JOIN node n ON n.nid = tn.nid
  JOIN node_images ni ON ni.nid = n.nid
  WHERE tn.tid IN (...)
) t WHERE rn = 1;

優勢：語義明確、確定性結果、支援任意排序規則、標準 SQL。

語法順序鐵律

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
-- GROUP BY 必須在 ORDER BY 和 LIMIT 之前

掌握 DISTINCT 邊界、GROUP BY 去重本質、窗口函數現代解法，讓去重邏輯從「試錯」變為「確定性工程」。