SQL 查詢與刪除重複記錄：單欄位、多欄位、ROW_NUMBER、CTE、游標五大方案全解析

場景定義

• 完全重複：所有欄位值相同
• 業務鍵重複：關鍵欄位（如 PeopleID, Seq）相同，其他欄位可能不同
• 目標：保留 1 筆（通常保留最早/最小 ID）

方案一：單欄位重複 — 子查詢 + IN

-- 查詢
SELECT * FROM People
WHERE PeopleID IN (SELECT PeopleID FROM People GROUP BY PeopleID HAVING COUNT(*) > 1);

-- 刪除（保留最小 RowID）
DELETE FROM People
WHERE PeopleID IN (SELECT PeopleID FROM People GROUP BY PeopleID HAVING COUNT(*) > 1)
  AND RowID NOT IN (SELECT MIN(RowID) FROM People GROUP BY PeopleID 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
);

-- 刪除（保留最小 RowID）
DELETE FROM Vitae a
WHERE (a.PeopleID, a.Seq) IN (
    SELECT PeopleID, Seq FROM Vitae GROUP BY PeopleID, Seq HAVING COUNT(*) > 1
)
  AND a.RowID NOT IN (
    SELECT MIN(RowID) FROM Vitae GROUP BY PeopleID, Seq HAVING COUNT(*) > 1
);

注意：某些資料庫不支援 WHERE (col1, col2) IN (...) 語法，需改用 EXISTS 或 JOIN。

方案三：ROW_NUMBER() 視窗函數（SQL 2005+ 推薦）

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY PeopleID, Seq ORDER BY RowID) AS RN
    FROM Vitae
)
DELETE FROM Vitae
WHERE RowID IN (SELECT RowID FROM CTE WHERE RN > 1);

• 可靈活控制保留策略：ORDER BY RowID ASC 保留最早，DESC 保留最新
• 可擴展至任意分組欄位組合
• 單次掃描，效能優於子查詢

方案四：游標迴圈（舊版/極特殊邏輯）

DECLARE @Max INT, @ID INT;
DECLARE cur CURSOR LOCAL FOR
    SELECT MainField, COUNT(*) FROM TableName GROUP BY MainField HAVING COUNT(*) > 1;

OPEN cur;
FETCH NEXT FROM cur INTO @ID, @Max;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Max = @Max - 1;
    SET ROWCOUNT @Max;  -- 只影響後續單一陳述式
    DELETE FROM TableName WHERE MainField = @ID;
    FETCH NEXT FROM cur INTO @ID, @Max;
END
CLOSE cur; DEALLOCATE cur;
SET ROWCOUNT 0;

缺點：逐組執行 DELETE，大量資料極慢；SET ROWCOUNT 已棄用（SQL 2012+），建議改用 TOP (@Max)。

方案五：完全重複 — DISTINCT 重建表

SELECT DISTINCT * INTO #Tmp FROM TableName;
DROP TABLE TableName;
SELECT * INTO TableName FROM #Tmp;
DROP TABLE #Tmp;

或加上唯一索引防止再犯：ALTER TABLE TableName ADD CONSTRAINT UQ_Key UNIQUE (Col1, Col2...)

效能與適用性對比

避坑檢查清單

1. 確認重複判定鍵正確（業務鍵 vs 全欄位）
2. 確認保留策略（最早/最新/特定條件）
3. 大表操作前完整備份，維護窗口執行
4. 檢查外鍵/觸發器/索引檢視表相依性
5. 刪除後執行 DBCC CHECKIDENT / 索引重建