Oracle中删除重复记录整理

Oracle中删除重复记录整理

Oracle中经常会删除一些重复记录,整理一下以备用

举例(建立数据如下):

1
2
3
4
5
6
7
8
9
10
create table t_table  
(id NUMBER,
name VARCHAR2(20)
);
insert into t_table values (1234, 'abc');
insert into t_table values (1234, 'abc');
insert into t_table values (1234, 'abc');
insert into t_table values (3456, 'bcd');
insert into t_table values (3456, 'bcd');
insert into t_table values (7890, 'cde');

1 .第一种方法:适用于有少量重复记录的情况(临时表法)

  • (建一个临时表用来存放重复的记录)
  • (清空表的数据,但保留表的结构)
  • (再将临时表里的内容反插回来)
1
2
3
create table tmp_table as select distinct * from t_table;  
truncate table t_table;
insert into t_table select * from tmp_table;

2 .第二种方法:适用于有大量重复记录的情况

1
2
3
4
5
6
delete t_table where   
(id,name) in (select id,name
from t_table group by id,name having count(*)>1)
and
rowid not in (select min(rowid)
from t_table group by id,name having count(*)>1);

3 .第三种方法:适用于有少量重复记录的情况

1
2
delete from t_table a where a.rowid!=(select max(b.rowid)   
from t_table b where a.id=b.id and a.name=b.name);
hyhcoder wechat
扫码关注我的个人订阅号