SQL 重複記錄查詢與刪除大全：單欄/多欄、保留首行、多表去重實戰

資料去重是資料治理高頻需求。本文系統整理單欄/多欄重複查詢、刪除策略（保留 MIN(rowid)）、多表關聯去重與游標批次處理，適用 MySQL/Oracle/SQL Server（語法微調）。

一、單欄重複處理

1. 查詢重複

SELECT * FROM people
WHERE peopleId IN (
  SELECT peopleId FROM people
  GROUP BY peopleId HAVING COUNT(peopleId) > 1
);

2. 刪除重複（保留 rowid 最小）

DELETE FROM people
WHERE peopleId IN (
  SELECT peopleId FROM people
  GROUP BY peopleId HAVING COUNT(peopleId) > 1
)
AND rowid NOT IN (
  SELECT MIN(rowid) FROM people
  GROUP BY peopleId HAVING COUNT(peopleId) > 1
);

二、多欄組合重複處理

1. 查詢重複

SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
  SELECT peopleId, seq FROM vitae
  GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

2. 刪除重複（保留 rowid 最小）

DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (
  SELECT peopleId, seq FROM vitae
  GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND rowid NOT IN (
  SELECT MIN(rowid) FROM vitae
  GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

3. 查詢重複（不含首行）

SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
  SELECT peopleId, seq FROM vitae
  GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND rowid NOT IN (
  SELECT MIN(rowid) FROM vitae
  GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

三、聚合統計視角

-- 單欄重複計數
SELECT Name, COUNT(*) FROM A GROUP BY Name HAVING COUNT(*) > 1;

-- 多欄重複計數
SELECT Name, Sex, COUNT(*) FROM A GROUP BY Name, Sex HAVING COUNT(*) > 1;

四、進階刪除策略

方法一：游標循環（適用大量重複、需分批提交）

DECLARE @max INT, @id INT;
DECLARE cur CURSOR LOCAL FOR
  SELECT 主鍵, COUNT(*) FROM 表名
  GROUP BY 主鍵 HAVING COUNT(*) > 1;
OPEN cur;
FETCH cur INTO @id, @max;
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @max = @max - 1;
  SET ROWCOUNT @max;
  DELETE FROM 表名 WHERE 主鍵 = @id;
  FETCH cur INTO @id, @max;
END
CLOSE cur; DEALLOCATE cur;
SET ROWCOUNT 0;

方法二：臨時表去重（全欄完全重複）

SELECT DISTINCT * INTO #Tmp FROM tableName;
DROP TABLE tableName;
SELECT * INTO tableName FROM #Tmp;
DROP TABLE #Tmp;
-- 最後建議加上唯一索引防止復發

方法三：保留首行的關鍵欄去重

SELECT IDENTITY(INT,1,1) AS autoID, * INTO #Tmp FROM tableName;
SELECT MIN(autoID) AS autoID INTO #Tmp2 FROM #Tmp GROUP BY Name, Address;
SELECT * FROM #Tmp WHERE autoID IN (SELECT autoID FROM #Tmp2);

五、MySQL 專用簡化寫法

-- 8.0+ CTE 寫法
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn
  FROM t
)
DELETE FROM t WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

六、根治建議

• 源頭加 UNIQUE INDEX 或 PRIMARY KEY
• ETL 層做 INSERT IGNORE / ON DUPLICATE KEY UPDATE
• 定期排程 pt-archiver 或 DELETE ... LIMIT 分批清理歷史髒資料

去重不只是刪資料，更是建立「唯一性約束」與「資料質量門禁」的過程。