場景一：基礎統計 — 查出重複值及其計數

-- 查找 id 重複的記錄及重複次數
SELECT id, COUNT(id) AS cnt
FROM `table`
GROUP BY id
HAVING COUNT(id) > 1;

• GROUP BY id 分組；
• HAVING COUNT(id) > 1 過濾重複組。

場景二：頻次排序 — 找出重複次數最多的值

-- 查詢 name 出現次數最多的記錄（Top 1）
SELECT name, COUNT(*) AS cnt
FROM data
GROUP BY name
ORDER BY cnt DESC
LIMIT 1;

-- 不加 LIMIT 可獲取完整重複頻次排行榜

場景三：多字段組合 — 各字段獨立取「出現最多、並列取最新」的一行

業務需求

表 face 有字段 eye, nose, mouth, create_time。需分別找出：
• eye 中出現次數最多的值（並列取 create_time 最大者）
• nose 同理
• mouth 同理
最終輸出一行：eye, nose, mouth。

表數據樣例

+----+-----+------+-------+---------------------+
| id | eye | nose | mouth | create_time         |
+----+-----+------+-------+---------------------+
|  1 | a1  | b1   | c1    | 2015-04-07 20:40:11 |
|  2 | a1  | b2   | c3    | 2015-04-07 20:40:28 |
|  3 | a2  | b2   | c1    | 2015-04-07 20:40:52 |
|  4 | a2  | b3   | c2    | 2015-04-07 20:41:03 |
|  5 | a3  | b4   | c4    | 2015-04-07 20:41:19 |
+----+-----+------+-------+---------------------+

解決方案：子查詢 + MAX(create_time) 打破並列

SELECT eye.eye, nose.nose, mouth.mouth
FROM (
    SELECT eye, MAX(create_time) AS max_time, COUNT(*) AS cnt
    FROM face
    GROUP BY eye
    ORDER BY cnt DESC, max_time DESC
    LIMIT 1
) AS eye,
(
    SELECT nose, MAX(create_time) AS max_time, COUNT(*) AS cnt
    FROM face
    GROUP BY nose
    ORDER BY cnt DESC, max_time DESC
    LIMIT 1
) AS nose,
(
    SELECT mouth, MAX(create_time) AS max_time, COUNT(*) AS cnt
    FROM face
    GROUP BY mouth
    ORDER BY cnt DESC, max_time DESC
    LIMIT 1
) AS mouth;

結果：

+-----+------+-------+
| eye | nose | mouth |
+-----+------+-------+
| a2  | b2   | c1    |
+-----+------+-------+

關鍵點解析

• 子查詢獨立聚合每個字段，ORDER BY cnt DESC, MAX(create_time) DESC 確保「數量優先、時間次之」。
• 必須用 MAX(create_time) 而非直接 create_time：
  GROUP BY 時非聚合列取值不確定（通常取組內第一行），若 a2 第二行時間較新但第一行較舊，直接用 create_time 會誤判。
  驗證：

-- 正確：用 MAX(create_time)
SELECT eye, MAX(create_time), COUNT(*) FROM face GROUP BY eye ORDER BY COUNT(*) DESC, MAX(create_time) DESC LIMIT 1;
-- eye=a2, max_time=20:41:03

-- 錯誤：直接用 create_time
SELECT eye, create_time, COUNT(*) FROM face GROUP BY eye ORDER BY COUNT(*) DESC, create_time DESC LIMIT 1;
-- eye=a2, create_time=20:40:52 (取到第一行時間，邏輯錯誤)

總結

場景	核心語法	適用範圍
基礎重複統計	GROUP BY + HAVING COUNT(*) > 1	單字段去重分析
重複頻次排序	GROUP BY + ORDER BY COUNT(*) DESC [+ LIMIT]	熱點值發現
多字段獨立取最多/最新	子查詢 + MAX(time) 打破並列 + 笛卡爾積組合	多維度特徵提取