表中存在重複業務鍵(如「採集時間」),需刪除重複行,保留每組第一筆(依主鍵 ID 最小)。
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;
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() 單陳述式刪除重複,大表分批處理。根本解決請加唯一約束/過濾索引從源頭阻擋髒資料寫入。