oracle 刪除重複資料的幾種方法

create table test_0210(id number,name varchar2(32),age number);
insert into test_0210 values(1,'abc',32);
insert into test_0210 values(2,'def',33);
insert into test_0210 values(3,'def',45);
commit;

SQL> select * from test_0210;
 
        ID NAME                                    AGE
---------- -------------------------------- ----------
         1 abc                                      32
         2 def                                      33
         3 def                                      45
 
1.使用rowid 效率高 直接定位到資料塊地址,根據需要取max rowid 或 min rowid 只適合刪除少量重複資料

SQL> delete from test_0210 where rowid not in (select max(rowid) from test_0210 group by name);
 
1 row deleted
 
SQL> select * from test_0210;
 
        ID NAME                                    AGE
---------- -------------------------------- ----------
         1 abc                                      32
         3 def                                      45

2.利用ID   只適合刪除少量重複資料

SQL> delete from test_0210 where id not  in (select max(id) from test_0210 group by name);
 
1 row deleted
 
SQL> select * from test_0210;
 
        ID NAME                                    AGE
---------- -------------------------------- ----------
         1 abc                                      32
         3 def                                      45

3.建立臨時表 ,這種方法適合刪除大量重複資料

SQL> create table test_temp as select * from test_0210 where id in (select max(id) from test_0210 group by name)
  2  ;
 
Table created
 
SQL> truncate table test_0210;
 
Table truncated
 
SQL> insert into test_0210 select * from test_temp;
 
2 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_0210;
 
        ID NAME                                    AGE
---------- -------------------------------- ----------
         1 abc                                      32
         3 def                                      45

4:也是建立臨時表 這裡用的是分析函式

SQL> create table test_temp as select id,name,age from (select row_number() over(partition by name order by id) rn ,id,name,age
  2                  from test_0210) where rn=1;
 
Table created
 
SQL> truncate table test_0210;
 
Table truncated
 
SQL> insert into test_0210 select * from test_temp;
 
2 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_0210;
 
        ID NAME                                    AGE
---------- -------------------------------- ----------
         1 abc                                      32
         2 def                                      33

 

總結刪除重複資料的方法很多,具體看需求,而選擇最符合自己的sql