oracle 查找、删除重复记录

您当前所在位置：首页 → 数据库 → Oracle → oracle 查找、删除重复记录

oracle 查找、删除重复记录 时间：2015/6/28来源：绿色软件之家作者：网管联盟我要评论(0)

　　总结了一下删除重复记录的方法，以及每种方法的优缺点。

　　假设表名为Tbl，表中有三列col1，col2，col3，其中col1，col2是主键，并且，col1，col2上加了索引。

　　1、通过创建临时表

　　可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：

　　creat table tbl_tmp (select distinct* from tbl);

　　truncate table tbl;//清空表记录

　　insert into tbl select * from tbl_tmp;//将临时表中的数据插回来。

　　这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。

　　2、利用rowid

　　在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。SQL语句如下：

　　delete from tbl where rowid in (select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)

　　如果已经知道每条记录只有一条重复的，这个sql语句适用。但是如果每条记录的重复记录有N条，这个N是未知的，就要考虑适用下面这种方法了。

　　3、利用max或min函数

　　这里也要使用rowid，与上面不同的是结合max或min函数来实现。SQL语句如下

　　delete from tbl a where rowid not in (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里max使用min也可以

　　或者用下面的语句

　　delete from tbl a where rowid < (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2

　　4、利用group by，提高效率

　　平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时，系统提示 ORA-01452 ：不能创建唯一索引，发现重复记录。

　　下面总结一下几种查找和删除重复记录的方法（以表CZ为例）：

　　表CZ的结构如下：

　　SQL> desc cz

　　Name Null? Type

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

　　C1 NUMBER(10)

　　C10 NUMBER(5)

　　C20 VARCHAR2(3)

　　删除重复记录的方法原理：

　　(1).在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。

　　(2).在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。

　　重复记录判断的标准是：

　　C1,C10和C20这三列的值都相同才算是重复记录。

　　经查看表CZ总共有16条记录：

　　SQL>set pagesize 100

　　SQL>select * from cz;

　　C1 C10 C20

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

　　1 2 dsf

　　2 3 che

　　1 2 dsf

　　2 3 che

　　3 4 dff

　　4 5 err

　　5 3 dar

　　6 1 wee

　　7 2 zxc

　　20 rows selected.

#p#副标题#e#

　　1.查找重复记录的几种方法：

　　(1).SQL>select * from cz group by c1,c10,c20 having count(*) >1;

　　C1 C10 C20

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

　　1 2 dsf

　　2 3 che

　　3 4 dff

　　(2).SQL>select distinct * from cz;

　　C1 C10 C20

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

　　1 2 dsf

　　2 3 che

　　3 4 dff

　　(3).SQL>select * from cz a where rowid=(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);

　　C1 C10 C20

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

　　1 2 dsf

　　2 3 che

　　3 4 dff

　　2.删除重复记录的几种方法：

　　(1).适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候，用以下语句效率会很高)：

　　SQL>delete cz where (c1,c10,c20) in (select c1,c10,c20 from cz group by c1,c10,c20 having count(*)>1) and rowid not in

　　(select min(rowid) from cz group by c1,c10,c20 having count(*)>1);

　　SQL>delete cz where rowid not in(select min(rowid) from cz group by c1,c10,c20);

　　(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)：

　　SQL>delete from cz a where a.rowid!=(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);

　　SQL>delete from cz a where a.rowid<(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);

　　SQL>delete from cz a where rowid <(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);

　　(3).适用于有少量重复记录的情况(临时表法)：

　　SQL>create table test as select distinct * from cz; (建一个临时表test用来存放重复的记录）

关键词标签：oracle

相关阅读

文章评论

查看所有0条评论>>