SQL Server 2000 清除重復數據兩種經典方法：游標循環 vs 單條 SQL 集合操作

場景

表 evaeve 中按 E_RecordID 分組，每組保留最大 id (最新記錄)，刪除其餘重復行。

方法一：游標循環 (RBAR，逐行處理，教學/極小數據量)

DECLARE 
    @id1 INT, @oldid INT, 
    @e_RecordID INT, @Old_e_RecordID INT;

DECLARE price CURSOR FOR
SELECT id, E_RecordID FROM evaeve ORDER BY E_RecordID DESC;

OPEN price;
FETCH NEXT FROM price INTO @oldid, @Old_e_RecordID;

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM price INTO @id1, @e_RecordID;

    IF @Old_e_RecordID = @e_RecordID
    BEGIN
        -- 同組非最大 id，刪除
        DELETE FROM evaeve WHERE id = @id1;
    END

    SET @oldid = @id1;
    SET @Old_e_RecordID = @e_RecordID;
END

CLOSE price;
DEALLOCATE price;

方法二：單條 SQL 集合操作 (高性能、標準寫法，強烈推薦)

DELETE FROM evaeve 
WHERE id NOT IN (
    SELECT MAX(id) FROM evaeve GROUP BY E_RecordID
);

深度對比與擴展

最佳實踐：NOT EXISTS 版本 (兼容 NULL、全版本)

DELETE FROM evaeve e1
WHERE EXISTS (
    SELECT 1 FROM evaeve e2
    WHERE e2.E_RecordID = e1.E_RecordID
      AND e2.id > e1.id
);

邏輯：存在同組且 id 更大的行 → 刪除當前行。等價於保留每組最大 id。

SQL Server 2005+ 現代寫法：ROW_NUMBER() 窗口函數

;WITH CTE AS (
    SELECT id, 
           ROW_NUMBER() OVER (PARTITION BY E_RecordID ORDER BY id DESC) AS rn
    FROM evaeve
)
DELETE FROM CTE WHERE rn > 1;

• 可靈活控制保留策略：ORDER BY id DESC 保留最大，ASC 保留最小，CreateTime DESC 保留最新。
• 支持多字段分組：PARTITION BY Col1, Col2。
• 執行計劃優化好，大數據量首選。

大表分批刪除模板 (避免日志爆漲/鎖升級)

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

WHILE @RowCount > 0
BEGIN
    DELETE TOP (@BatchSize) FROM evaeve
    WHERE id NOT IN (
        SELECT MAX(id) FROM evaeve GROUP BY E_RecordID
    );
    SET @RowCount = @@ROWCOUNT;
    -- 可選：WAITFOR DELAY '00:00:01' 降低鎖競爭
END

重復數據預防：唯一約束/索引

-- 業務鍵唯一，防止重復入庫
ALTER TABLE evaeve ADD CONSTRAINT UQ_evaeve_RecordID UNIQUE (E_RecordID);

-- 或允許歷史但標識最新
ALTER TABLE evaeve ADD IsLatest BIT DEFAULT 1;
CREATE UNIQUE INDEX UX_evaeve_Latest ON evaeve (E_RecordID) WHERE IsLatest = 1; -- 過濾索引 (2008+)