SQL Server 刪除重複資料三大經典場景完整解法

資料表中出現重複列是常見資料品質問題。本文針對三種最典型的重複資料場景，提供經過生產環境驗證的標準解法，並說明各方法的適用條件與效能特性。

場景一：依業務鍵去重，保留第一筆（無主鍵/無身分識別欄）

需求： Users 表有 u_name、u_pwd，u_name 有重複，需刪除重複名稱，保留第一筆。

方法：暫存表 + 迴圈逐筆檢查（教學示範，效能較差）

DECLARE @id INT = 1, @u_name VARCHAR(50), @u_pwd VARCHAR(50);
CREATE TABLE #new_users (u_name VARCHAR(50), u_pwd VARCHAR(50));

WHILE @id < 1000
BEGIN
    IF EXISTS (SELECT u_name FROM Users WHERE u_id = @id)
    BEGIN
        SELECT @u_name = u_name, @u_pwd = u_pwd FROM Users WHERE u_id = @id;
        IF NOT EXISTS (SELECT u_name FROM #new_users WHERE u_name = @u_name)
            INSERT INTO #new_users (u_name, u_pwd) VALUES (@u_name, @u_pwd);
    END
    SET @id += 1;
END

SELECT * FROM #new_users;
-- 後續：重命名原表 → #new_users 改名為 Users

⚡ 生產環境推薦：ROW_NUMBER() 窗口函數（單陳述式、高效能）

WITH CTE AS (
    SELECT 
        u_name, u_pwd,
        ROW_NUMBER() OVER (PARTITION BY u_name ORDER BY u_id) AS rn
    FROM Users
)
DELETE FROM CTE WHERE rn > 1;

場景二：有主鍵 (ID)，依業務鍵去重，保留最小 ID

需求： Users 表有主鍵 id，業務鍵 name 重複，保留每組最小 id。

標準三步驟法（相容所有版本）

-- 1. 找出每組保留的 ID
SELECT MIN(id) AS id INTO #tmp_keep FROM Users GROUP BY name;

-- 2. 依保留 ID 選取完整資料建立新表
SELECT u.* INTO #tmp_data FROM Users u JOIN #tmp_keep k ON u.id = k.id;

-- 3. 切換表身分
DROP TABLE Users;
EXEC sp_rename '#tmp_data', 'Users'; -- 或 SELECT * INTO Users FROM #tmp_data; DROP TABLE #tmp_data;

-- 清理
DROP TABLE #tmp_keep;

⚡ 進階：無主鍵時先加身分識別欄

SELECT IDENTITY(INT,1,1) AS autoID, * INTO #tmp_with_id FROM Users;
-- 之後按 autoID 當主鍵處理

場景三：完全重複列（所有欄位相同），只保留一筆

需求： 整列資料完全重複，去重保留單一列。

最簡單：DISTINCT 導出新表

SELECT DISTINCT * INTO #tmp_unique FROM Users;
DROP TABLE Users;
SELECT * INTO Users FROM #tmp_unique;
DROP TABLE #tmp_unique;

注意：此法會移除所有索引、條件約束、觸發器、權限。執行前請腳本化所有衍生物件，事後重建。

方法選擇決策表

防重複的根本對策

1. 資料庫層：加上 UNIQUE 條件約束或 PRIMARY KEY。
2. 應用層：寫入前檢查、使用 MERGE / INSERT ... WHERE NOT EXISTS 模式。
3. ETL 層：資料清洗階段去重，配合 CHECKSUM/HASHBYTES 快速比對。