ORACLE中删除重复记录

ORACLE中删除重复记录平时工作中可能会遇见当试图对库表中的某一列或几列创建唯一索引时，系统提示ora-01452：不能创建唯一索引，发现重复记录。下面总结一下几种查找和删除重复记录的方法（以表cz为例）：表cz的结构如下：sqldesccznamenull?type-------------------------------------------------------------------c1number(10)c10number(5)c20varchar2(3)删除重复记录的方法原理：(1).在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在oracle中的哪一个数据文件、块、行上。(2).在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就能了，其余全部删除。重复记录判断的标准是：c1,c10和c20这三列的值都相同才算是重复记录。经查看表cz总共有16条记录：sqlsetpagesize100sqlselect*fromcz;c1c10c20-----------------------12dsf12dsf12dsf12dsf23che12dsf12dsf12dsf12dsf23che23che23che23che34dff34dff34dff45err53dar61wee72zxc20rowsselected.1.查找重复记录的几种方法：(1).sqlselect*fromczgroupbyc1,c10,c20havingcount(*)1;c1c10c20-----------------------12dsf23che34dff(2).sqlselectdistinct*fromcz;c1c10c20-----------------------12dsf23che34dff(3).sqlselect*fromczawhererowid=(selectmax(rowid)fromczwherec1=a.c1andc10=a.c10andc20=a.c20);c1c10c20-----------------------12dsf23che34dff2.删除重复记录的几种方法：(1).适用于有大量重复记录的情况(在c1,c10和c20列上建有索引的时候，用以下语句效率会非常高)：sqldeleteczwhere(c1,c10,c20)in(selectc1,c10,c20fromczgroupbyc1,c10,c20havingcount(*)1)androwidnotin(selectmin(rowid)fromczgroupbyc1,c10,c20havingcount(*)1);sqldeleteczwhererowidnotin(selectmin(rowid)fromczgroupbyc1,c10,c20);(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会非常低)：sqldeletefromczawherea.rowid!=(selectmax(rowid)fromczbwherea.c1=b.c1anda.c10=b.c10anda.c20=b.c20);sqldeletefromczawherea.rowid(selectmax(rowid)fromczbwherea.c1=b.c1anda.c10=b.c10anda.c20=b.c20);sqldeletefromczawhererowid(selectmax(rowid)fromczwherec1=a.c1andc10=a.c10andc20=a.c20);(3).适用于有少量重复记录的情况(临时表法)：sqlcreatetabletestasselectdistinct*fromcz;(建一个临时表test用来存放重复的记录）sqltruncatetablecz;(清空cz表的数据，但保留cz表的结构）sqlinsertintoczselect*fromtest;(再将临时表test里的内容反插回来）(4).适用于有大量重复记录的情况(exceptioninto子句法)：采用altertable命令中的exceptioninto子句也能确定出库表中重复的记录。这种方法稍微麻烦一些，为了使用“excepeioninto”子句，必须首先创建exceptions表。创建该表的sql脚本文件为utlexcpt.sql。对于win2000系统和unix系统，oracle存放该文件的位置稍有不同，在win2000系统下，该脚本文件存放在$oracle_home＼ora90＼rdbms＼admin目录下；而对于unix系统，该脚本文件存放在$oracle_home/rdbms/admin目录下。具体步骤如下：sql@?/rdbms/admin/utlexcpt.sqltablecreated.sqldescexceptionsnamenull?type---------------------------------------------------------------row_idrowidownervarchar2(30)table_namevarchar2(30)constraintvarchar2(30)sqlaltertableczaddconstraintcz_uniqueunique(c1,c10,c20)exceptionsintoexceptions;*erroratline1:ora-02299:cannotvalidate(test.cz_unique)-duplicatekeysfoundsqlcreatetabledupsasselect*fromczwhererowidin(selectrow_idfromexceptions);tablecreated.sqlselect*fromdups;c1c10c20-----------------------12dsf12dsf12dsf12dsf23che12dsf12dsf12dsf12dsf23che23che23che23che34dff34dff34dff16rowsselected.sqlselectrow_idfromexceptions;row_id------------------aaahd/aaiaaaadsaaaaaahd/aaiaaaadsaabaaahd/aaiaaaadsaacaaahd/aaiaaaadsaafaaahd/aaiaaaadsaahaaahd/aaiaaaadsaaiaaahd/aaiaaaadsaagaaahd/aaiaaaadsaadaaahd/aaiaaaadsaaeaaahd/aaiaaaadsaajaaahd/aaiaaaadsaakaaahd/aaiaaaadsaalaaahd/aaiaaaadsaamaaahd/aaiaaaadsaanaaahd/aaiaaaadsaaoaaahd/aaiaaaadsaap16rowsselected.sqldeletefromczwhererowidin(selectrow_idfromexceptions);16rowsdeleted.sqlinsertintoczselectdistinct*fromdups;3rowscreated.sqlselect*fromcz;c1c10c20-----------------------12dsf23che34dff45err53dar61wee72zxc7rowsselected.从结果里能看到重复记录已删除。3.实例：数据库中有deny_mobile表需要按照mobile去重复，删除重复记录为：DeletefromSMS_DENYMOBILEWHEREmobileIN(SELECTmobileFROMSMS_DENYMOBILEGROUPBYmobileHAVINGCOUNT(*)1)ANDROWIDNOTIN(SELECTMIN(ROWID)FROMSMS_DENYMOBILEGROUPBYmobileHAVINGCOUNT(*)1)