資料去重是 ETL、資料治理、系統遷移的高頻剛需。本文系統梳理「保留最小/最大 ID」、「多欄位組合去重」、「軟刪除/歸檔」等主流模式,給出生產級可執行腳本,並揭露 `ROWID` 等 Oracle 殘留語法在 SQL Server 中的陷阱。
CREATE TABLE dbo.DupDemo (
ID INT IDENTITY(1,1) PRIMARY KEY, -- 代理鍵,唯一遞增
Name NVARCHAR(50),
Email NVARCHAR(100),
CreateTime DATETIME DEFAULT GETDATE()
);
INSERT dbo.DupDemo (Name, Email) VALUES
(N'張三','zhang@test.com'), (N'張三','zhang@test.com'), -- 完全重複
(N'李四','li@test.com'), (N'李四','li@test.com'), -- 完全重複
(N'王五','wang@test.com'), (N'王五','wang2@test.com'); -- Name 相同但 Email 不同
-- 1. 確認重複分布
SELECT Name, COUNT(*) AS Cnt FROM dbo.DupDemo GROUP BY Name HAVING COUNT(*) > 1;
-- 2. 刪除重複,保留每組最小 ID
DELETE d
FROM dbo.DupDemo d
WHERE d.ID NOT IN (
SELECT MIN(ID) FROM dbo.DupDemo GROUP BY Name
);
-- 以 (Name, Email) 組合判定重複
DELETE d
FROM dbo.DupDemo d
WHERE d.ID NOT IN (
SELECT MIN(ID) FROM dbo.DupDemo GROUP BY Name, Email
);
;WITH Ranked AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Name, Email ORDER BY ID) AS RN
FROM dbo.DupDemo
)
DELETE FROM Ranked WHERE RN > 1;
-- 進階:保留最新記錄 (ORDER BY CreateTime DESC)
;WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, Email ORDER BY CreateTime DESC) AS RN
FROM dbo.DupDemo
)
DELETE FROM Ranked WHERE RN > 1;
-- 新增標記欄位
ALTER TABLE dbo.DupDemo ADD IsDeleted BIT NOT NULL DEFAULT 0;
;WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, Email ORDER BY ID) AS RN
FROM dbo.DupDemo WHERE IsDeleted = 0
)
UPDATE Ranked SET IsDeleted = 1 WHERE RN > 1;
| 錯誤寫法 | 原因 | 正確寫法 |
|---|---|---|
| `WHERE rowid NOT IN ...` | `rowid` 是 Oracle 偽欄位,SQL Server **不存在** | 使用主鍵 `ID` 或 `ROW_NUMBER()` |
| `DELETE FROM table WHERE col IN (SELECT col ...)` 直接刪 | 若子查詢返回重複值,可能誤刪非重複行 | 配合 `NOT IN (SELECT MIN(PK) ...)` 或 `ROW_NUMBER()` |
| 大表直接 `DELETE` 無批次 | 交易日誌暴漲、長時間鎖表、TempDB 壓力 | 分批刪除 (每批 5000-10000 行) + `CHECKPOINT` |