IF OBJECT_ID('duplicate_all') IS NOT NULL DROP TABLE duplicate_all;
CREATE TABLE duplicate_all (c1 INT, c2 INT, c3 VARCHAR(100));
INSERT duplicate_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');
SELECT DISTINCT * INTO #tmp FROM duplicate_all WHERE c1 = 1;
DELETE duplicate_all WHERE c1 = 1;
INSERT duplicate_all SELECT * FROM #tmp;
大表可用
TRUNCATE TABLE duplicate_all替代DELETE,避免日誌暴增,但需注意身分識別種子重置。
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY c1,c2,c3 ORDER BY (SELECT 0)) AS rn
FROM duplicate_all WHERE c1 = 1
)
DELETE cte WHERE rn > 1;
SELECT * INTO NewTable FROM TableA
UNION -- 自動去重
SELECT * FROM TableB;
IF OBJECT_ID('duplicate_col') IS NOT NULL DROP TABLE duplicate_col;
CREATE TABLE duplicate_col (
c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(100)
);
INSERT duplicate_col VALUES
(1,100,'aaa'),(2,100,'aaa'),(3,100,'aaa'),(4,100,'aaa'),(5,500,'eee');
IF OBJECT_ID('tmp') IS NOT NULL DROP TABLE tmp;
CREATE TABLE tmp (
c1 INT, c2 INT, c3 VARCHAR(100),
CONSTRAINT UQ_01 UNIQUE(c2,c3) WITH (IGNORE_DUP_KEY = ON)
);
INSERT tmp SELECT * FROM duplicate_col; -- 重複列自動忽略並產生警告
SELECT * FROM tmp; -- 保留首筆
-- 保留 c1 最小
DELETE FROM duplicate_col
WHERE EXISTS (
SELECT 1 FROM duplicate_col b
WHERE duplicate_col.c1 > b.c1
AND duplicate_col.c2 = b.c2
AND duplicate_col.c3 = b.c3
);
-- 等價寫法
DELETE FROM duplicate_col
WHERE c1 NOT IN (SELECT MIN(c1) FROM duplicate_col GROUP BY c2, c3);
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY c2,c3 ORDER BY c1) AS rn
FROM duplicate_col
)
DELETE cte WHERE rn > 1; -- 保留每組第一筆 (依 c1 排序)
-- 保留第 2 筆:WHERE rn <> 2
-- 保留前 3 筆:WHERE rn > 3
DELETE TOP (10000) ... 迴圈,配合 WAITFOR DELAY '00:00:01'SWITCH PARTITION 秒級歸檔/清除BULK_LOGGED 模式 + TABLOCK (需評估風險)MERGE / INSERT ... ON DUPLICATE KEY)完全重複用 DISTINCT/ROW_NUMBER,部分重複用 ROW_NUMBER 或 NOT IN (MIN/PK)。ROW_NUMBER() 是通用最佳解:語法清晰、效能穩定、支援保留任意第 N 筆。生產環境大量刪除務必分批、備份、驗證。