1、Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中
那些具有最大rowid的就可以了,其余全部删除。
实现方法:
SQL> create table t (id varchar2(2),name varchar2(10));
Table created.
SQL> insert into t values('1','t');
1 row created.
SQL> insert into t values('1','t');
1 row created.
SQL> insert into t values('1','t');
1 row created.
SQL> insert into t values('2','t');
1 row created.
SQL> insert into t values('2','t');
1 row created.
SQL> commit;
Commit complete.
SQL> delete from t a where a.rowid!=(select max(rowid) from t b where a.id=b.id
and a.name=b.name);
3 rows deleted.
SQL> select * from t;
ID NAME
-- ----------
1 t
2 t
SQL> commit;
Commit complete.
查询表中多余的重复记录
select * from t where id in(select id from t group by id having count(id)>1 );
删除记录
delete from t where id in(select id from t group by id having count(id)>1 ) and rowid not in(select min(rowid) from t group by id having count(id)>1);