Oracle 刪除重複資料與去重複查詢實用語法大全

場景定義

假設 EMPLOYEE 表包含 EMP_ID（主鍵）、EMP_NAME，需以姓名判斷重複，保留最小 ID 的那一筆。

一、查詢重複資料

-- 僅顯示重複的姓名與次數
select emp_name, count(*)
from employee
group by emp_name
having count(*) > 1;

二、查詢可刪除的重複列（保留最小 ID）

select t1.*
from employee t1
where t1.emp_name in (
    select t2.emp_name
    from employee t2
    group by t2.emp_name
    having count(*) > 1
)
and t1.emp_id not in (
    select min(t3.emp_id)
    from employee t3
    group by t3.emp_name
    having count(*) > 1
);

三、刪除重複資料（保留最小 ID）

delete from employee t1
where t1.emp_name in (
    select t2.emp_name
    from employee t2
    group by t2.emp_name
    having count(*) > 1
)
and t1.emp_id not in (
    select min(t3.emp_id)
    from employee t3
    group by t3.emp_name
    having count(*) > 1
);

四、進階去重技巧

1. 使用 ROWID（效能最佳）

delete from employee
where rowid not in (
    select min(rowid)
    from employee
    group by emp_name
);

2. 使用分析函數 ROW_NUMBER()（10g+）

delete from employee
where emp_id in (
    select emp_id from (
        select emp_id,
               row_number() over (partition by emp_name order by emp_id) rn
        from employee
    )
    where rn > 1
);

3. 多欄位判斷重複（如姓名+身分證）

delete from employee
where rowid not in (
    select min(rowid)
    from employee
    group by emp_name, id_card
);

五、執行前備份建議

-- 建立備份表
create table employee_bak as select * from employee;

-- 或匯出關鍵資料
-- 刪除後立即驗證
select emp_name, count(*) from employee group by emp_name having count(*) > 1;

六、效能比較與適用場景