萬盛學電腦網

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

SQL Server 刪除重複資料完整指南:完全重複 vs 部分欄位重複、多種刪除技巧

重複資料分類

  1. 完全重複:所有欄位值相同 (通常缺主鍵/唯一約束)
  2. 部分欄位重複:關鍵業務欄位重複,但有主鍵區分 (程式邏輯缺陷)

一、刪除完全重複記錄

測試資料建立

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

方法 1:暫存表去重 (適合中小表)

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,避免日誌暴增,但需注意身分識別種子重置。

方法 2:ROW_NUMBER() 窗口函數 (SQL 2005+ 推薦)

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;

方法 3:多表 UNION 去重 (跨表完全重複)

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

方法 1:唯一索引 IGNORE_DUP_KEY (一次性清理)

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;  -- 保留首筆

方法 2:保留主鍵最小/最大值刪除

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

方法 3:ROW_NUMBER() 保留第 N 筆

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 筆。生產環境大量刪除務必分批、備份、驗證。

copyright © 萬盛學電腦網 all rights reserved