SQL 查找與刪除重複資料的多種方法

單一欄位判斷重複

查找重複

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

刪除重複（保留 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
);

多欄位判斷重複

查找重複

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

刪除重複（保留 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
);

查找重複（不含保留列）

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
);

進階技巧：使用 ROW_NUMBER()（SQL 2005+）

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

此法語法簡潔、效能佳，支援多欄位 PARTITION BY，為現代 SQL Server 首選做法。

注意事項

• rowid 為 SQL Server 內部虛擬欄位，僅限堆疊表；有叢集索引表請改用主鍵或 %%physloc%%。
• 刪除前務必先 SELECT 確認將保留哪些列。
• 大量刪除建議分批次，避免交易記錄暴增與長時間鎖定。