SQL Server 刪除重複資料實戰：ROW_NUMBER() 視窗函數精準去重

業務場景：定義重複標準

表 @InvoiceListMaster 中，CompanyName + InvoiceNumber + CustomerNumber 三欄位完全相同視為重複資料，需保留第一筆刪除其餘。

測試資料建立

DECLARE @InvoiceListMaster TABLE (
  ID INT IDENTITY PRIMARY KEY,
  CompanyName NCHAR(20),
  InvoiceNumber INT,
  CustomerNumber INT,
  RmaNumber INT
);

INSERT @InvoiceListMaster VALUES
(N'華為', 1001, 100, 200),  -- 重複組 1
(N'華為', 1001, 100, 300),  -- 重複組 1
(N'華為', 1001, 100, 301),  -- 重複組 1
(N'中興', 1002, 200, 1),    -- 重複組 2
(N'中興', 1002, 200, 2);    -- 重複組 2

刪除邏輯：ROW_NUMBER() 分組排序 + 自聯結刪除

DELETE A 
FROM (
  SELECT 
    ROW_NUMBER() OVER(PARTITION BY CompanyName, InvoiceNumber, CustomerNumber 
                      ORDER BY CompanyName, InvoiceNumber, CustomerNumber) AS rown,
    CompanyName, InvoiceNumber, CustomerNumber
  FROM @InvoiceListMaster
) A
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT 
      ROW_NUMBER() OVER(PARTITION BY CompanyName, InvoiceNumber, CustomerNumber 
                        ORDER BY CompanyName, InvoiceNumber, CustomerNumber) AS rown,
      CompanyName, InvoiceNumber, CustomerNumber
    FROM @InvoiceListMaster
  ) B
  WHERE B.CompanyName = A.CompanyName
    AND B.InvoiceNumber = A.InvoiceNumber
    AND B.CustomerNumber = A.CustomerNumber
    AND A.rown > B.rown  -- 保留 rown=1，刪除 rown>1
);

核心技術點解析

1. PARTITION BY 定義去重鍵：依業務鍵分組
2. ORDER BY 決定保留哪一行：此處順序無差異，實務可用 ID ASC 保留最早建立、RmaNumber DESC 保留最新等
3. ROW_NUMBER() 產生組內序號：rown=1 為保留目標
4. 自聯結比較 rown：刪除 rown > 1 的行

現代簡化寫法（SQL 2005+ / 通用 CTE）

;WITH cte AS (
  SELECT ID,
    ROW_NUMBER() OVER(PARTITION BY CompanyName, InvoiceNumber, CustomerNumber ORDER BY ID) AS rn
  FROM @InvoiceListMaster
)
DELETE FROM @InvoiceListMaster 
WHERE ID IN (SELECT ID FROM cte WHERE rn > 1);

優勢：可讀性高、避免重複計算 ROW_NUMBER()、標準 SQL 寫法。

驗證結果

SELECT * FROM @InvoiceListMaster;
-- 僅保留：
-- 1, 華為, 1001, 100, 200
-- 4, 中興, 1002, 200, 1

延伸場景與注意事項

• 大表效能：去重鍵建立索引加速 PARTITION BY 排序
• 保留策略靈活化：ORDER BY 可依業務調整（最早/最新/金額最大等）
• 合併而非刪除：若需合併屬性（如 RmaNumber 取最大值），改用 GROUP BY + 聚合函數 + MERGE 或 刪除重插
• 觸發器影響：刪除操作會觸發 DELETE 觸發器，大量刪除考慮分批或暫停觸發器