Oracle中经常会删除一些重复记录,整理一下以备用
举例:
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');
第一种方法:
适用于有少量重复记录的情况(临时表法):
--(建一个临时表用来存放重复的记录)
--(清空表的数据,但保留表的结构)
--(再将临时表里的内容反插回来)
create table tmp_table as select distinct * from t_table;
truncate table t_table;
insert into t_table select * from tmp_table;
第二种方法
适用于有大量重复记录的情况
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);
第三种方法:
适用于有少量重复记录的情况
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);