萬盛學電腦網

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

SQL Server 刪除指定重複記錄:保留單筆的高效 T-SQL 模式

問題定義

表中存在重複業務鍵(如「採集時間」),需刪除重複行,保留每組第一筆(依主鍵 ID 最小)。

標準解法:CTE + ROW_NUMBER() (SQL Server 2005+)

WITH DupCTE AS (
    SELECT 
        ID,
        CollectTime,
        ROW_NUMBER() OVER (PARTITION BY CollectTime ORDER BY ID) AS rn
    FROM dbo.SensorData
)
DELETE FROM DupCTE WHERE rn > 1;
  • PARTITION BY 重複判斷欄位
  • ORDER BY 決定保留哪一筆 (ID 最小 = 最早插入)
  • 單次陳述式、集合操作、效能最佳

舊版相容寫法:相關子查詢 (SQL Server 2000)

DELETE FROM SensorData
WHERE ID > (
    SELECT MIN(ID) 
    FROM SensorData s2 
    WHERE s2.CollectTime = SensorData.CollectTime
);
  • 每行執行子查詢,大表效能差
  • 文章原寫法 TOP 1 ... HAVING COUNT(*) > 1 需迴圈執行,極不推薦

大量資料分批刪除 (避免記錄檔暴漲/鎖升級)

DECLARE @BatchSize INT = 5000, @RowsDeleted INT = 1;

WHILE @RowsDeleted > 0
BEGIN
    DELETE TOP (@BatchSize) FROM SensorData
    WHERE ID IN (
        SELECT ID FROM (
            SELECT ID, ROW_NUMBER() OVER (PARTITION BY CollectTime ORDER BY ID) AS rn
            FROM SensorData
        ) t WHERE rn > 1
    );
    SET @RowsDeleted = @@ROWCOUNT;
    -- 可選:WAITFOR DELAY '00:00:01' 降低衝擊
END

識別重複群組統計

SELECT CollectTime, COUNT(*) AS dup_count, MIN(ID) AS keep_id
FROM SensorData
GROUP BY CollectTime
HAVING COUNT(*) > 1
ORDER BY dup_count DESC;

預防重複:唯一約束/索引

-- 業務鍵唯一約束 (允許單一 NULL)
ALTER TABLE SensorData ADD CONSTRAINT UQ_SensorData_CollectTime UNIQUE (CollectTime);

-- 或過濾索引 (SQL Server 2008+,允許 NULL 但非 NULL 唯一)
CREATE UNIQUE INDEX UX_SensorData_CollectTime_NotNull 
ON SensorData(CollectTime) WHERE CollectTime IS NOT NULL;

總結

現代 SQL Server 請使用 CTE + ROW_NUMBER() 單陳述式刪除重複,大表分批處理。根本解決請加唯一約束/過濾索引從源頭阻擋髒資料寫入。

copyright © 萬盛學電腦網 all rights reserved