如何查找、删除表中重复的记录

 

如何查找、删除表中重复的记录

 

 

软件环境：

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为：C:/ORANT

 

问题提出：

1、当我们想要为一个表创建唯一索引时，如果该表有重复的记录，则无法创建成功。

方法原理：

1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，

　 rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。

 

2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中

　 那些具有最大rowid的就可以了，其余全部删除。

 

3、以下语句用到了3项技巧：rowid、子查询、别名。

 

实现方法：

SQL> create table a (

 2 bm char(4),            --编码

 3 mc varchar2(20)            --名称

 4 )

 5 /

 

表已建立.

 

SQL> insert into a values('1111','1111');

SQL> insert into a values('1112','1111');

SQL> insert into a values('1113','1111');

SQL> insert into a values('1114','1111');

 

SQL> insert into a select * from a;

 

插入4个记录.

 

SQL> commit;

 

完全提交.

 

SQL> select rowid,bm,mc from a;

 

ROWID              BM   MC

------------------ ---- -------

000000D5.0000.0002 1111 1111

000000D5.0001.0002 1112 1111

000000D5.0002.0002 1113 1111

000000D5.0003.0002 1114 1111

000000D5.0004.0002 1111 1111

000000D5.0005.0002 1112 1111

000000D5.0006.0002 1113 1111

000000D5.0007.0002 1114 1111

 

查询到8记录.

 

 

查出重复记录

SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

 

ROWID              BM   MC

------------------ ---- --------------------

000000D5.0000.0002 1111 1111

000000D5.0001.0002 1112 1111

000000D5.0002.0002 1113 1111

000000D5.0003.0002 1114 1111

 

删除重复记录

SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

 

删除4个记录.

 

SQL> select rowid,bm,mc from a;

 

ROWID              BM   MC

------------------ ---- --------------------

000000D5.0004.0002 1111 1111

000000D5.0005.0002 1112 1111

000000D5.0006.0002 1113 1111

000000D5.0007.0002 1114 1111

 

come from :: http://dev.youkuaiyun.com/article/59/59333.shtm

-测试数据

/*-----------------------------

select * from tt

-----------------------------*/

id          pid        

----------- -----------

1           1

1           1

2           2

3           3

3           3

3           3

 

（所影响的行数为 6 行）

 

首先，如何查询table中有重复记录

select *,count(1) as rownum

from tt

group by id, pid

having count(1) > 1

id          pid         rownum     

----------- ----------- -----------

1           1           2

3           3           3

 

（所影响的行数为 2 行）

 

方法一:使用distinct和临时表

if object_id('tempdb..#tmp') is not null

drop table #tmp

select distinct * into #tmp from tt

truncate table tt

insert into tt select * from #tmp

 

方法二:添加标识列

alter table tt add NewID int identity(1,1)

go 

delete from tt where exists(select 1 from tt a where a.newid>tt.newid and tt.id=a.id and tt.pid=a.pid)

go

alter table tt drop column NewID

go

 

--测试结果

/*-----------------------------

select * from tt

-----------------------------*/

id          pid        

----------- -----------

1           1

2           2

3           3

 

（所影响的行数为 3 行）

*---*-- * 8 8 * * * * 8* * * * 8 8 *

USE CEO

CREATE TABLE TT

(

TTNO CHAR(4),

TTNAME VARCHAR(10)

)

 

INSERT INTO TT (TTNO,TTNAME) VALUES ('1425','WHERE')

INSERT INTO TT (TTNO,TTNAME) VALUES ('1425','WHERE')

INSERT INTO TT (TTNO,TTNAME) VALUES ('1424','WHEREIS')

INSERT INTO TT (TTNO,TTNAME) VALUES ('1435','WHEREIS')

INSERT INTO TT (TTNO,TTNAME) VALUES ('1435','WHEREIS')

方法二:添加标识列(最有效方法)

alter table tt add newid2 int identity(1,1)

go

delete from tt where exists( select 1 from tt   a where a.newid2>tt.newid2 and tt.ttno=a.ttno and tt.ttname=a.ttname)

alter table tt drop column newid2

go

select * from tt