SQL 去重保留最新：GROUP BY + MAX 高效寫法

需求

表 test，列 A 可能重複，需按 A 分組，保留 B (時間) 最大的一行。

標準寫法

SELECT A, MAX(B) AS LatestTime
FROM test
GROUP BY A
HAVING COUNT(*) > 1;  -- 僅輸出有重複的組

需保留其他列

若需保留整行（如 C, D），用窗口函數：

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY A ORDER BY B DESC) AS rn
    FROM test
)
SELECT A, B, C, D
FROM Ranked
WHERE rn = 1;

MySQL 8.0- / 不支持窗口函數

SELECT t1.*
FROM test t1
JOIN (
    SELECT A, MAX(B) AS maxB
    FROM test
    GROUP BY A
) t2 ON t1.A = t2.A AND t1.B = t2.maxB;

性能優化

• 為 (A, B) 建立聯合索引：CREATE INDEX idx_a_b ON test(A, B);。
• 大表分批處理：WHERE A BETWEEN ? AND ? 分頁去重。
• 刪除重複保留一條：

  DELETE t1 FROM test t1
  JOIN test t2 ON t1.A = t2.A AND t1.B < t2.B;

適用場景對比