SQL Server 刪除重複資料完整攻略：有無主鍵、單欄與多欄判斷

情境一：資料表有主鍵 (PK)

單一 PK 欄位

DELETE FROM 用戶表
WHERE id NOT IN (
    SELECT MAX(id) FROM 用戶表 GROUP BY col1, col2, col3...
);

GROUP BY 後的欄位即為判斷重複的依據。

複合主鍵

-- 找出重複群組
SELECT * FROM 用戶表
WHERE col1+','+col2+...+col5 IN (
    SELECT MAX(col1+','+col2+...+col5)
    FROM 用戶表
    GROUP BY col1,col2,col3,col4
    HAVING COUNT(*) > 1
);

或使用 EXISTS 寫法：

SELECT * FROM 用戶表 t1
WHERE EXISTS (
    SELECT 1 FROM 用戶表 t2
    WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2
    GROUP BY t2.col1, t2.col2 HAVING COUNT(*) > 1
);

全欄位比對（無主鍵欄位可用）

SELECT * INTO #aa FROM 用戶表 GROUP BY id1, id2...;
DELETE 用戶表;
INSERT INTO 用戶表 SELECT * FROM #aa;

情境二：資料表無主鍵

方法 1：臨時表加身分識別欄

SELECT IDENTITY(INT,1,1) AS id, * INTO #temp FROM 用戶表;
DELETE #temp WHERE id NOT IN (
    SELECT MAX(id) FROM #temp GROUP BY col1, col2, col3...
);
DELETE 用戶表;
INSERT INTO 用戶表(...) SELECT ... FROM #temp;

方法 2：直接加身分識別欄再刪除

ALTER TABLE 用戶表 ADD newField INT IDENTITY(1,1);
DELETE 用戶表
WHERE newField NOT IN (
    SELECT MIN(newField) FROM 用戶表
    GROUP BY 除 newField 外所有欄位
);
ALTER TABLE 用戶表 DROP COLUMN newField;

選擇建議

• 小資料量、有 PK：用 NOT IN (SELECT MAX(PK)...) 最簡潔。
• 大資料量：考慮 ROW_NUMBER() 配合 CTE 分批刪除，避免長時間鎖表。
• 無 PK：方法 2 較不需額外空間，但會鎖定原表較久；方法 1 較安全。