SQL Server 刪除重複記錄完整解決方案（保留單筆/批次清理）

核心概念：何謂「重複」？

本文處理的「重複」非指整列資料完全相同，而是特定欄位（如 Title）值重複的情況。資料表結構範例：

HZT 表結構
ID int (主鍵)
Title nvarchar(50) (業務鍵，可能重複)
AddDate datetime

一、查詢重複記錄的多種策略

1. 找出所有重複群組的完整資料

SELECT * FROM HZT 
WHERE Title IN (
    SELECT Title FROM HZT 
    GROUP BY Title 
    HAVING COUNT(*) > 1
);

此法會列出所有 Title 重複的完整列，包含每個群組的所有筆數。

2. 只顯示每群組的一筆代表資料（保留 ID 最大者）

SELECT * FROM HZT 
WHERE ID IN (
    SELECT MAX(ID) FROM HZT 
    GROUP BY Title
);

常用於預覽「保留哪一筆」的結果確認。

二、刪除重複資料的標準作法

⚠️ 危險操作：刪除所有重複群組（含第一筆）

DELETE FROM HZT 
WHERE Title IN (
    SELECT Title FROM HZT 
    GROUP BY Title 
    HAVING COUNT(*) > 1
);

極度不建議：此指令會清除整個重複群組，導致該業務鍵資料全失。

✅ 推薦作法：保留單筆（ID 最大者），刪除其餘

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

此為最通用、最安全的刪除策略，保留每群組最新（ID 最大）的一筆。

三、進階場景：依據排序欄位決定保留策略

當資料有「大小關係」（如 ID、日期、版本號）時，提供 7 種等效寫法，開發者可依習慣與效能選擇：

案例一：保留最小值（最早建立/最舊版本）

-- 方法 1：EXISTS 相關子查詢（直觀易讀）
DELETE a FROM #T a 
WHERE EXISTS (SELECT 1 FROM #T WHERE Name = a.Name AND ID < a.ID);

-- 方法 2：LEFT JOIN 找出非最小值
DELETE a FROM #T a 
LEFT JOIN (SELECT MIN(ID) AS ID, Name FROM #T GROUP BY Name) b 
    ON a.Name = b.Name AND a.ID = b.ID
WHERE b.ID IS NULL;

-- 方法 3：NOT IN 子查詢
DELETE a FROM #T a 
WHERE ID NOT IN (SELECT MIN(ID) FROM #T WHERE Name = a.Name);

-- 方法 4：群組後 NOT IN（需 ID 唯一）
DELETE a FROM #T a 
WHERE ID NOT IN (SELECT MIN(ID) FROM #T GROUP BY Name);

-- 方法 5：計數法
DELETE a FROM #T a 
WHERE (SELECT COUNT(1) FROM #T WHERE Name = a.Name AND ID < a.ID) > 0;

-- 方法 6：TOP 1 排序法
DELETE a FROM #T a 
WHERE ID <> (SELECT TOP 1 ID FROM #T WHERE Name = a.Name ORDER BY ID);

-- 方法 7：ANY 比較運算子
DELETE a FROM #T a 
WHERE ID > ANY (SELECT ID FROM #T WHERE Name = a.Name);

案例二：保留最大值（最新建立/最新版本）

-- 核心邏輯：將所有「<」改為「>」，「MIN」改為「MAX」，「ASC」改為「DESC」
DELETE a FROM #T a 
WHERE EXISTS (SELECT 1 FROM #T WHERE Name = a.Name AND ID > a.ID);

DELETE a FROM #T a 
WHERE ID NOT IN (SELECT MAX(ID) FROM #T WHERE Name = a.Name);

DELETE a FROM #T a 
WHERE ID <> (SELECT TOP 1 ID FROM #T WHERE Name = a.Name ORDER BY ID DESC);

DELETE a FROM #T a 
WHERE ID < ANY (SELECT ID FROM #T WHERE Name = a.Name);

四、特殊情況：完全重複列（無主鍵/無排序欄位）

當資料完全相同（含所有欄位），無法用 ID 區分時：

方法 1：DISTINCT 暫存表法（最簡單）

SELECT DISTINCT * INTO #Temp FROM #T; -- 去重存入暫存表
TRUNCATE TABLE #T;                    -- 清空原表
INSERT INTO #T SELECT * FROM #Temp;   -- 寫回去重後資料

方法 2：動態加身分欄位法

ALTER TABLE #T ADD ID INT IDENTITY; -- 加入流水號
DELETE a FROM #T a 
WHERE EXISTS (SELECT 1 FROM #T WHERE Num = a.Num AND Name = a.Name AND ID > a.ID);
ALTER TABLE #T DROP COLUMN ID;      -- 移除流水號

方法 3：游標逐群處理法（大量資料適用）

DECLARE cur CURSOR LOCAL FOR
SELECT COUNT(1)-1, Num, Name FROM #T 
GROUP BY Num, Name HAVING COUNT(1) > 1;

DECLARE @cnt INT, @num INT, @name NVARCHAR(1);
OPEN cur;
FETCH NEXT FROM cur INTO @cnt, @num, @name;
WHILE @@FETCH_STATUS = 0 BEGIN
    SET ROWCOUNT @cnt;              -- 限制刪除筆數
    DELETE #T WHERE Num = @num AND Name = @name;
    SET ROWCOUNT 0;                 -- 重置限制
    FETCH NEXT FROM cur INTO @cnt, @num, @name;
END
CLOSE cur; DEALLOCATE cur;

五、效能與選擇建議

六、實務防呆檢查清單

• ✅ 先用 SELECT 驗證要刪除的筆數與內容
• ✅ 確認交易隔離層級，避免長時間鎖表
• ✅ 大量刪除考慮分批次（TOP 1000 迴圈）減少交易日誌
• ✅ 刪除前備份或建立快照
• ✅ 若有外鍵關聯，先處理子表資料

掌握這套完整方案，即能從容應對各類 SQL Server 重複資料清理需求。