SQL 重復記錄查詢與刪除全集：單字段、多字段、ROWID、游標四大類方案對比

重復記錄治理是數據清洗高頻需求。本文匯總四大類經典方案，涵蓋單/多字段判斷、保留首行/末行、大數據量效能優化。

場景定義

• 完全重復：所有列值相同。
• 關鍵字段重復：如 Name、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
);

適用：中小表、有 RowId/主鍵標識行順序。

方案二：多字段去重（組合鍵）

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

方案三：標準 SQL 聚合統計（無需 RowId）

-- 僅統計重復組合
SELECT Name, Sex, COUNT(*) 
FROM A 
GROUP BY Name, Sex 
HAVING COUNT(*) > 1;

方案四：完全重復行去重（DISTINCT + 重建表）

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

方案六：游標循環刪除（大數據量、無索引表）

DECLARE @Max INT, @Id INT;
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT KeyCol, COUNT(*) FROM TableName
    GROUP BY KeyCol HAVING COUNT(*) > 1;

OPEN Cur;
FETCH NEXT FROM Cur INTO @Id, @Max;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Max = @Max - 1;           -- 保留 1 行
    SET ROWCOUNT @Max;             -- 限制影響行數
    DELETE FROM TableName WHERE KeyCol = @Id;
    FETCH NEXT FROM Cur INTO @Id, @Max;
END
CLOSE Cur; DEALLOCATE Cur;
SET ROWCOUNT 0;

優點：避免大事務日志爆漲、鎖升級。缺點：慢、需串行執行。

方案七：現代寫法 ROW_NUMBER()（SQL 2005+ 推薦）

WITH CTE AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY KeyCol ORDER BY Id) AS Rn
    FROM TableName
)
DELETE FROM CTE WHERE Rn > 1;  -- 保留每組第一行

優勢：單語句、集合操作、可加索引提示、支援多字段 PARTITION BY Col1, Col2。

效能對比與選型建議

最佳實踐清單

1. 優先 ROW_NUMBER() CTE 刪除，配合 PARTITION BY 多字段去重。
2. 大表分批刪除：DELETE TOP (10000) FROM CTE WHERE Rn > 1 循環直到 @@ROWCOUNT=0。
3. 刪除前備份，或先 SELECT INTO #Backup 保留重復行供審計。
4. 治理後立即添加 UNIQUE INDEX 或 PRIMARY KEY 從源頭防重。