SQL 刪除重複記錄的多種經典方法總結

測試資料

CREATE TABLE employee (emp_id INT, emp_name VARCHAR(20), salary DECIMAL(10,2));
INSERT INTO employee VALUES
(1, 'sunshine', 10000),
(1, 'sunshine', 10000),
(2, 'semon', 20000),
(2, 'semon', 20000),
(3, 'xyz', 30000);

方法一：建立暫存表去重（最通用、效能佳）

SELECT DISTINCT * INTO #tmp FROM employee;
TRUNCATE TABLE employee;
INSERT INTO employee SELECT * FROM #tmp;
DROP TABLE #tmp;

方法二：使用 ROWID (Oracle) / 物理定位刪除

-- Oracle 專用：保留最大 ROWID
DELETE FROM employee e2
WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM employee e1
                    WHERE e1.emp_id = e2.emp_id
                      AND e1.emp_name = e2.emp_name
                      AND e1.salary = e2.salary);

SQL Server 無 ROWID，可用 %%physloc%% (SQL 2008+) 或主鍵/唯一鍵替代。

方法三：GROUP BY + HAVING 找出重複群組，再刪除

-- 找出重複群組
SELECT emp_id, emp_name, salary, COUNT(*) AS cnt
FROM employee
GROUP BY emp_id, emp_name, salary
HAVING COUNT(*) > 1;

-- 刪除重複 (保留最小 ID 假設有身分識別欄)
-- 若表有主鍵 id：
DELETE e1 FROM employee e1
JOIN (
    SELECT emp_id, emp_name, salary, MIN(id) AS keep_id
    FROM employee
    GROUP BY emp_id, emp_name, salary
    HAVING COUNT(*) > 1
) e2 ON e1.emp_id = e2.emp_id AND e1.emp_name = e2.emp_name AND e1.salary = e2.salary
WHERE e1.id > e2.keep_id;

方法四：使用 ROW_NUMBER() 視窗函數 (SQL 2005+)

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY emp_id, emp_name, salary ORDER BY (SELECT 0)) AS rn
    FROM employee
)
DELETE FROM cte WHERE rn > 1;

最簡潔、效能最好、支援任意重複定義，推薦為首選。

方法五：NOT EXISTS 相關子查詢

DELETE FROM employee e1
WHERE EXISTS (
    SELECT 1 FROM employee e2
    WHERE e2.emp_id = e1.emp_id
      AND e2.emp_name = e1.emp_name
      AND e2.salary = e1.salary
      AND e2.%%physloc%% < e1.%%physloc%%  -- 或主鍵比較
);

最佳實踐建議

1. 資料表**務必加主鍵或唯一索引**，從源頭防止重複。
2. SQL Server 2005+ 優先使用 ROW_NUMBER() CTE 刪除法。
3. 大量資料刪除時分批處理，避免記錄檔暴漲與長時間鎖定。
4. 刪除前先備份或交易包裹，確保可回滾。