SQL Server 刪除重復數據完整指南：完全重復與部分重復的 5 種高效方法

場景分類

• 完全重復：所有列值相同，通常因缺少主鍵/唯一約束。
• 部分重復：關鍵業務列（如身份證、手機號）重復，表有主鍵但業務邏輯缺陷導致。

測試數據準備

-- 完全重復表
CREATE TABLE dup_all (c1 INT, c2 INT, c3 VARCHAR(100));
INSERT INTO dup_all VALUES
(1,100,'aaa'),(1,100,'aaa'),(1,100,'aaa'),(1,100,'aaa'),(1,100,'aaa'),
(2,200,'bbb'),(3,300,'ccc'),(4,400,'ddd'),(5,500,'eee');

-- 部分重復表（有主鍵）
CREATE TABLE dup_col (c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(100));
INSERT INTO dup_col VALUES
(1,100,'aaa'),(2,100,'aaa'),(3,100,'aaa'),(4,100,'aaa'),(5,500,'eee');

方法一：臨時表去重（完全重復，小表適用）

SELECT DISTINCT * INTO #tmp FROM dup_all WHERE c1 = 1;
DELETE dup_all WHERE c1 = 1;
INSERT INTO dup_all SELECT * FROM #tmp;
DROP TABLE #tmp;

優點：簡單直觀。缺點：大表鎖表久、產生大量日志。

方法二：ROW_NUMBER() 窗口函數（通用、高效）

-- 完全重復
WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY c1,c2,c3 ORDER BY (SELECT 0)) AS rn
    FROM dup_all WHERE c1 = 1
)
DELETE cte WHERE rn > 1;

-- 部分重復（按 c2,c3 分組，保留 c1 最小）
WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY c2,c3 ORDER BY c1) AS rn
    FROM dup_col
)
DELETE cte WHERE rn > 1;

優點：單語句、可指定保留策略（最小/最大/最新）、大表高效。

方法三：唯一索引 IGNORE_DUP_KEY（部分重復，一次性清理）

CREATE TABLE tmp (
    c1 INT, c2 INT, c3 VARCHAR(100),
    CONSTRAINT UQ_tmp UNIQUE (c2,c3) WITH (IGNORE_DUP_KEY = ON)
);
INSERT INTO tmp SELECT * FROM dup_col; -- 重複行自動忽略，只保留首行
SELECT * FROM tmp; -- 結果：c1=1,2,5 三行

⚠️ 注意：IGNORE_DUP_KEY 僅對 INSERT 生效，UPDATE 仍報錯；SQL Server 2016+ 建議改用 ROW_NUMBER。

方法四：NOT IN / NOT EXISTS 關聯刪除（部分重復，經典寫法）

-- 保留每組 c1 最小
DELETE FROM dup_col
WHERE c1 NOT IN (SELECT MIN(c1) FROM dup_col GROUP BY c2, c3);

-- 或 EXISTS 寫法（通常性能更好）
DELETE FROM dup_col d1
WHERE EXISTS (
    SELECT 1 FROM dup_col d2
    WHERE d2.c2 = d1.c2 AND d2.c3 = d1.c3 AND d2.c1 < d1.c1
);

方法五：多表聯合去重（跨表完全重復）

SELECT * INTO new_table FROM table1
UNION -- 自動去重
SELECT * FROM table2
UNION
SELECT * FROM table3;

性能與選型建議

預防重復的長效機制

1. 業務主鍵務必建立 PRIMARY KEY / UNIQUE CONSTRAINT。
2. 關鍵業務列（身份證、手機、訂單號）加 UNIQUE INDEX。
3. 應用層冪等性設計：幂等 Key、分布式鎖、數據庫唯一約束作為最後防線。
4. 定期執行 DBCC CHECKCONSTRAINTS 檢測約束違規。