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

針對無主鍵/唯一索引的測試表出現重複資料，整理四種刪除重複記錄的實用 SQL 寫法，並展示查詢重複資料的技巧。

測試環境

EMP_ID   NUMBER(10)
EMP_NAME VARCHAR2(20)
SALARY   NUMBER(10,2)

資料含三組重複：(1, sunshine, 10000)×2、(2, semon, 20000)×3、(3, xyz, 30000)×1。

查詢重複記錄

-- 去重顯示
SELECT DISTINCT * FROM employee;

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

-- 利用 ROWID 保留每組最大 ROWID
SELECT * FROM employee e1
WHERE rowid IN (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);

刪除重複記錄四大法

1. 建臨時表法（最通用、跨資料庫）

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

2. ROWID NOT IN 最大值法（Oracle 特有）

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

3. ROWID < 最大值法（同理可用 MIN）

DELETE FROM employee e2
WHERE rowid < (SELECT MAX(e1.rowid) FROM employee e1
  WHERE e1.emp_id=e2.emp_id AND e1.emp_name=e2.emp_name AND e1.salary=e2.salary);

4. 單語句 GROUP BY ROWID 法（效率最高）

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

適用場景建議

• 資料量小、一次性清理 → 臨時表法最直觀。
• Oracle 環境、需保留 ROWID 特性 → 第 4 種單語句效率最佳。
• MySQL/SQL Server 無 ROWID → 採用臨時表法或 ALTER IGNORE TABLE ... ADD UNIQUE INDEX（舊版支援）。