SQL 刪除重複資料的多種經典方法與效能比較

前言：重複資料的來源與危害

缺乏主鍵/唯一約束的資料表在大量匯入、合併、同步時極易產生重複列。重複資料不僅浪費儲存空間，更會導致彙總統計錯誤、應用邏輯異常。本文以 employee 表為例，系統整理從查詢識別到刪除清理的完整方案集。

範例資料與環境

-- 表結構
CREATE TABLE employee (
    emp_id    NUMBER(10),
    emp_name  VARCHAR2(20),
    salary    NUMBER(10,2)
);

-- 範例資料 (含重複)
emp_id | emp_name | salary
-------|----------|-------
1      | sunshine | 10000
1      | sunshine | 10000   -- 重複
2      | semon    | 20000
2      | semon    | 20000   -- 重複
3      | xyz      | 30000
2      | semon    | 20000   -- 重複 (第三筆)

方法一：DISTINCT 查詢 + 重建表 (適用於小表、一次性清理)

-- 1. 建立暫存表保留唯一值
CREATE TABLE employee_dedup AS
SELECT DISTINCT * FROM employee;

-- 2. 刪除原表並重命名 (需注意權限、索引、觸發器、外鍵重建)
DROP TABLE employee;
RENAME employee_dedup TO employee;
-- 之後需重建 PK、索引、授權...

優點： 邏輯簡單、無需複雜 SQL。
缺點： 鎖表時間長、需重建相依物件、不適用大表線上環境。

方法二：GROUP BY + HAVING 識別重複群組

-- 查詢重複的鍵值組合
SELECT emp_id, emp_name, salary, COUNT(*) AS dup_cnt
FROM employee
GROUP BY emp_id, emp_name, salary
HAVING COUNT(*) > 1;

結果可作為後續刪除條件依據。

方法三：ROWID 保留最早/最新一筆 (Oracle 特有，高效)

-- 保留每組最早插入的列 (MIN ROWID)
DELETE FROM employee e1
WHERE ROWID > (
    SELECT MIN(ROWID)
    FROM employee e2
    WHERE e1.emp_id = e2.emp_id
      AND e1.emp_name = e2.emp_name
      AND e1.salary = e2.salary
);

-- 或保留最新 (MAX ROWID)
DELETE FROM employee e1
WHERE ROWID < (
    SELECT MAX(ROWID)
    FROM employee e2
    WHERE e1.emp_id = e2.emp_id
      AND e1.emp_name = e2.emp_name
      AND e1.salary = e2.salary
);

原理： Oracle ROWID 編碼資料檔案/區塊/列位置，唯一標識物理列，單表掃描即可完成，效能極佳。

方法四：暫存表 + 截斷原表 + 回寫 (通用、可控)

-- 1. 建立暫存表存唯一值
CREATE GLOBAL TEMPORARY TABLE tmp_employee_unique
ON COMMIT PRESERVE ROWS
AS SELECT DISTINCT * FROM employee;

-- 2. 清空原表 (TRUNCATE 速度快、不產生 UNDO、釋放高水位線)
TRUNCATE TABLE employee;

-- 3. 回寫唯一資料
INSERT INTO employee SELECT * FROM tmp_employee_unique;

-- 4. 重建索引、約束、授權...

適用： 大表、需保留表結構、可接受短暫鎖表的維護窗口。

方法五：SQL Server / PostgreSQL / MySQL 通用寫法 (CTE + ROW_NUMBER)

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY emp_id, emp_name, salary ORDER BY emp_id) AS rn
    FROM employee
)
DELETE FROM employee
WHERE (emp_id, emp_name, salary) IN (
    SELECT emp_id, emp_name, salary FROM cte WHERE rn > 1
);
-- 或直接 DELETE FROM cte WHERE rn > 1 (支援可更新 CTE 的資料庫)

優點： 標準 SQL、可攜性強、邏輯清晰、支援複雜排序保留規則 (如保留最新修改時間)。

效能與選型建議表

根治建議：從源頭防止重複

1. 設計階段：業務鍵務必定義 PRIMARY KEY 或 UNIQUE CONSTRAINT。
2. ETL 階段：啟用 MERGE / UPSERT 邏輯，避免盲目 INSERT。
3. 應用層：冪等性設計，重複提交不產生副作用。
4. 監控：定期排程檢查重複鍵值，異常告警。