如何查找、删除表中重复的记录
软件环境：
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.csdn.net/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
| 网站地图 |