SQL 刪除重複記錄多種實戰方法（以 Oracle 為例）

場景與樣本資料

表 EMPLOYEE (EMP_ID, EMP_NAME, SALARY) 含重複列：

EMP_ID EMP_NAME SALARY
1      sunshine 10000
1      sunshine 10000   ← 重複
2      semon    20000
2      semon    20000   ← 重複
3      xyz      30000
2      semon    20000   ← 重複

方法一：建立暫存表去重

CREATE TABLE temp_emp AS SELECT DISTINCT * FROM employee;
TRUNCATE TABLE employee;
INSERT INTO employee SELECT * FROM temp_emp;
DROP TABLE temp_emp;

簡單直觀，但需獨佔表鎖定，大表停機時間長。

方法二：利用 ROWID 保留最小/最大值

Oracle 每列擁有唯一 ROWID（檔案/區塊/列位址），重複資料列 ROWID 必異。保留每組最小 ROWID：

DELETE FROM employee e2
WHERE ROWID NOT IN (
    SELECT MIN(ROWID) FROM employee e1
    WHERE e1.emp_id = e2.emp_id
      AND e1.emp_name = e2.emp_name
      AND e1.salary = e2.salary
);

或使用相關子查詢：

DELETE FROM employee e2
WHERE ROWID < (
    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 搭配 GROUP BY 最大 ROWID（效率較高）

DELETE FROM employee
WHERE ROWID NOT IN (
    SELECT MAX(ROWID) FROM employee
    GROUP BY emp_id, emp_name, salary
);

僅掃描一次表即可完成分組與聚合，效能優於方法二。

方法四：分析函數 RANK() 彈性控制保留策略（推薦）

4.1 基礎用法：保留最小 ID

DELETE FROM employee
WHERE emp_id IN (
    SELECT emp_id FROM (
        SELECT emp_id,
               RANK() OVER (PARTITION BY emp_name, salary ORDER BY emp_id) AS rn
        FROM employee
    ) WHERE rn > 1
);

4.2 效能優化：先過濾重複群組再計算 RANK

DELETE FROM employee
WHERE emp_id IN (
    SELECT emp_id FROM (
        SELECT emp_id,
               RANK() OVER (PARTITION BY emp_name, salary ORDER BY emp_id) AS rn
        FROM employee
        WHERE (emp_name, salary) IN (
            SELECT emp_name, salary FROM employee
            GROUP BY emp_name, salary HAVING COUNT(*) > 1
        )
    ) WHERE rn > 1
);

測試 500 萬列、4.5 萬重複：
完整掃描版 135 秒 → 過濾版 47 秒（提升 65%）。

4.3 進階控制：保留最新/最舊、保留前 N 筆

• 保留最新記錄：ORDER BY rec_date DESC, emp_id
• 每組保留前 3 筆：HAVING COUNT(*) > 3 搭配 WHERE rn > 3

總結比較

建議：大數據量優先使用 RANK() 方案並加入重複群組預過濾；無主鍵表可改用全欄位 PARTITION BY。