SQL 查重與刪重完全手冊：單欄/多欄、保留最大/最小 ID、ROW_NUMBER 現代寫法

重複資料是資料品質頭號殺手。本文從傳統 GROUP BY ... HAVING 到現代 ROW_NUMBER() OVER，系統整理查重、刪重的各種場景與最佳寫法。

一、查找重複記錄

1. 單欄位判重（如 peopleId）

-- 顯示所有重複行（含第一筆）
SELECT * FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1
);

-- 僅顯示重複群組的「第 2 筆以後」（配合刪除用）
SELECT * FROM people p1
WHERE EXISTS (
    SELECT 1 FROM people p2
    WHERE p2.peopleId = p1.peopleId AND p2.rowid < p1.rowid  -- 假設有 rowid 或 PK
);

2. 多欄位組合判重（如 peopleId + seq）

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

二、刪除重複、保留一筆

傳統寫法（相容舊版）

-- 保留 MAX(ID)
DELETE FROM HZT
WHERE ID NOT IN (SELECT MAX(ID) FROM HZT GROUP BY Title);

-- 多欄位保留 MIN(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
);

現代寫法：ROW_NUMBER()（SQL Server 2005+、Oracle、PostgreSQL、MySQL 8.0+）

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY peopleId, seq ORDER BY rowid) AS rn
    FROM vitae
)
DELETE FROM cte WHERE rn > 1;

✅ 優點： 邏輯清晰、可指定保留策略（最早/最新/最大金額...）、效能優於 NOT IN 子查詢、支援複雜排序規則。

三、完全重複列（所有欄位相同）

-- 快速去重產生新表
SELECT DISTINCT * INTO #Tmp FROM TableName;
DROP TABLE TableName;
SELECT * INTO TableName FROM #Tmp;
DROP TABLE #Tmp;
-- 最後補上 PK/唯一索引防止復發

四、進階場景：保留第一筆、但需輸出被刪除的明細

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Name, Address ORDER BY CreatedAt) AS rn
    FROM TableName
)
-- 先輸出被刪除資料供稽核
SELECT * INTO #Deleted FROM cte WHERE rn > 1;
-- 再刪除
DELETE FROM cte WHERE rn > 1;

五、效能提示

• 大表刪重建議分批（如每批 5000 筆）配合 TOP 避免長交易鎖表
• 判重欄位務必有索引（複合索引順序 = PARTITION BY 順序）
• NOT IN 遇 NULL 會失效，改用 NOT EXISTS 或 LEFT JOIN ... WHERE right.key IS NULL

口訣： 查重用 GROUP BY ... HAVING COUNT(*) > 1，刪重首選 ROW_NUMBER() 保留 rn=1，大表分批跑，索引要跟上。