SQL 刪除重複資料四大經典方案完整解析

前言

資料表中出現重複列是資料庫維護常見議題。本文整理四種主流刪除重複資料的 SQL 方案，並說明適用情境與效能考量。

方案一：暫存表法（最直觀、適合中小資料量）

核心邏輯：將不重複資料暫存，清空原表，再寫回。

-- 1. 找出重複鍵值
SELECT key_col, COUNT(*) INTO #temp_dup FROM source_tbl GROUP BY key_col HAVING COUNT(*) > 1;

-- 2. 找出不重複鍵值
INSERT INTO #temp_dup
SELECT key_col, COUNT(*) FROM source_tbl GROUP BY key_col HAVING COUNT(*) = 1;

-- 3. 保留所有不重複資料
SELECT * INTO #temp_unique FROM source_tbl WHERE key_col IN (SELECT key_col FROM #temp_dup);

-- 4. 清空原表
DELETE FROM source_tbl;

-- 5. 寫回唯一資料
INSERT INTO source_tbl SELECT * FROM #temp_unique;

-- 6. 清理暫存表
DROP TABLE #temp_dup, #temp_unique;

優點：邏輯清晰，易理解維護。
缺點：大量資料時鎖表時間長，影響併發。

方案二：游標配合 SET ROWCOUNT（逐組刪除，適合極大資料量）

DECLARE @max INT, @id INT;
DECLARE cur CURSOR LOCAL FOR
    SELECT key_col, COUNT(*) FROM source_tbl GROUP BY key_col HAVING COUNT(*) > 1;

OPEN cur;
FETCH NEXT FROM cur INTO @id, @max;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @max = @max - 1;          -- 保留 1 筆
    SET ROWCOUNT @max;            -- 限制影響筆數
    DELETE FROM source_tbl WHERE key_col = @id;
    FETCH NEXT FROM cur INTO @id, @max;
END

CLOSE cur;
DEALLOCATE cur;
SET ROWCOUNT 0;

關鍵：SET ROWCOUNT n 讓後續 DELETE 只刪前 n 筆，確保每組至少保留一筆。
注意：SQL Server 2012+ 建議改用 TOP (n) 語法，SET ROWCOUNT 未來版本將移除。

方案三：動態 SQL 預存程序（泛用化、可重複使用）

CREATE PROCEDURE usp_RemoveDuplicates
    @table_name VARCHAR(100),
    @key_col VARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX), @id VARCHAR(100), @max INT, @type INT;

    -- 動態建立游標查重複鍵
    SET @sql = N'DECLARE cur CURSOR FOR SELECT ' + QUOTENAME(@key_col) + ', COUNT(*) 
                 FROM ' + QUOTENAME(@table_name) + ' GROUP BY ' + QUOTENAME(@key_col) + ' HAVING COUNT(*) > 1';
    EXEC sp_executesql @sql;

    OPEN cur;
    FETCH NEXT FROM cur INTO @id, @max;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @max = @max - 1;
        SET ROWCOUNT @max;

        -- 依鍵值資料型別動態建構刪除語句
        SELECT @type = xtype FROM syscolumns WHERE id = OBJECT_ID(@table_name) AND name = @key_col;
        IF @type = 56  -- INT
            SET @sql = N'DELETE FROM ' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@key_col) + ' = ' + @id;
        ELSE IF @type = 167  -- VARCHAR
            SET @sql = N'DELETE FROM ' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@key_col) + ' = ''' + @id + '''';

        EXEC sp_executesql @sql;
        FETCH NEXT FROM cur INTO @id, @max;
    END

    CLOSE cur; DEALLOCATE cur; SET ROWCOUNT 0;
END;

適用：需跨多張表、多種鍵型別通用刪重邏輯時。

方案四：現代語法（SQL Server 2005+ 推薦）— ROW_NUMBER() 窗口函數

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY (SELECT 0)) AS rn
    FROM source_tbl
)
DELETE FROM cte WHERE rn > 1;

優點：語法簡潔、效能最佳、無需暫存表或游標、單陳述式完成。
建議：新專案優先採用此法；舊版本 SQL Server 才考慮前三種方案。

總結比較表