萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 刪除重複資料保留一條:單欄位/多欄位去重完整方案與陷阱避坑

資料去重是 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:單欄位去重 (保留最小 ID)

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

模式 2:多欄位組合去重 (保留最小 ID)

-- 以 (Name, Email) 組合判定重複
DELETE d
FROM dbo.DupDemo d
WHERE d.ID NOT IN (
    SELECT MIN(ID) FROM dbo.DupDemo GROUP BY Name, Email
);

模式 3:使用 CTE + ROW_NUMBER() (通用、可預覽、支援複雜排序)

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

模式 4:軟刪除 / 歸檔 (不物理刪除)

-- 新增標記欄位
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;

⚠️ SQL Server 常見陷阱

大表分批刪除模板

DECLARE @BatchSize INT = 5000, @Deleted INT = 1;
WHILE @Deleted > 0
BEGIN
    DELETE TOP (@BatchSize) d
    FROM dbo.BigDupTable d
    WHERE d.ID NOT IN (
        SELECT MIN(ID) FROM dbo.BigDupTable GROUP BY DupKeyCol1, DupKeyCol2
    );
    SET @Deleted = @@ROWCOUNT;
    CHECKPOINT; -- 簡單模式下截斷日誌
    WAITFOR DELAY '00:00:00.1'; -- 降低鎖爭用
END

預防重複的長效機制

  1. **資料庫層**:加 `UNIQUE INDEX` / `UNIQUE CONSTRAINT` 於業務鍵
  2. **應用層**:`INSERT ... ON DUPLICATE KEY UPDATE` (MySQL) / `MERGE` / `TRY_CATCH` 捕獲 2627 錯誤
  3. **ETL 層**:源頭去重 → 暫存表 `ROW_NUMBER` 過濾 → 寫入目標表

去重一次易,防重長期難。**唯一約束 + 應用層冪等寫入** 才是治本之道。

copyright © 萬盛學電腦網 all rights reserved
錯誤寫法原因正確寫法
`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`