ORACLE中删除重复记录

《ORACLE中删除重复记录》由会员分享，可在线阅读，更多相关《ORACLE中删除重复记录（8页珍藏版）》请在装配图网上搜索。

1、平时工作中可能会遇见当试图对库表中的某一列或几列创建唯一索引时，ORACLE 中删除重复记录系统提示 ora-0145212dsf不能创建唯一索引，发现重复记录。下面总结一下几种查找和删除重复记录的方法(以表cz 为例)：表 cz 的结构如下：sqldesccznamenull?type c1number(10)c10number(5)c20varchar2(3)删除重复记录的方法原理：(1) . 在 oracle 中，每一条记录都有一个rowid ， rowid 在整个数据库中是唯一的， rowid 确定了每条记录是在oracle 中的哪一个数据文件、块、行上。(2) . 在重复的记录中，可

2、能所有列的内容都相同，但rowid 不会相同，所以只要确定出重复记录中那些具有最大rowid 的就能了，其余全部删除。重复记录判断的标准是：c1,c10 和 c20 这三列的值都相同才算是重复记录。经查看表 cz 总共有 16 条记录：sqlsetpagesize100sqlselect*fromcz;c1c10c2012dsf12dsf 12dsf23che 12dsf12dsf12dsf12dsf23che23che23che23che34dff34dff34dff45err53dar61wee72zxc20rowsselected.1 .查找重复记录的几种方法：(1).sqlselect

3、*fromczgroupbyc1,c10,c20havingcount(*)1;c1c10c2012dsf23che34dff(2).sqlselectdistinct*fromcz;12dsf 23che34dff(3).sqlselect*fromczawhererowid=(selectmax(rowid)fromczwherec1=a.c1andc10=a.c10andc20=a.c20);c1c10c2012dsf23che34dff2 . 删除重复记录的几种方法：(1) . 适用于有大量重复记录的情况(在 c1,c10 和 c20 列上建有索引的时候，用以下语句效率会非常高 ) ：

4、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.row

5、id!=(selectmax(rowid)fromczbwherea.c1=b.c1anda.c10= b.c10anda.c20=b.c20);sqldeletefromczawherea.rowiddeletefromczawhererowidcreatetabletestasselectdistinct*fromcz;(sqltruncatetablecz;（ 清空 cz 表的数据，但保留 cz 表的结构）sqlinsertintoczselect*fromtest;（ 再将临时表test 里的内容反插回来）(4) . 适用于有大量重复记录的情况（exceptioninto 子句法 ）

6、：采用 altertable 命令中的 exceptioninto 子句也能确定出库表中重复的记录。这种方法稍微麻烦一些，为了使用“ excepeioninto ”子句，必须首先创建exceptions 表。创建该表的 sql 脚本文件为 utlexcpt.sql 。对于 win2000 系统和 unix 系统， oracle 存放该文件的位置稍有不同，在 win2000 系统下，该脚本文件存放在$oracle_home ora90 rdbms admin 目录下；而对于 unix 系统，该脚本文件存放在$oracle_home/rdbms/admin 目录下。具体步骤如下：sql?/rdbm

7、s/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)-duplicatekeysfoundsqlcreateta

8、bledupsasselect*fromczwhererowidin(selectrow_idfromexceptions);tablecreated.sqlselect*fromdups;12dsf 12dsf12dsf12dsf23che12dsf12dsf12dsf12dsf23che23che23che23che34dff34dff34dff16rowsselected.sqlselectrow_idfromexceptions;row_id aaahd/aaiaaaadsaaa aaahd/aaiaaaadsaab aaahd/aaiaaaadsaac aaahd/aaiaaaads

9、aaf aaahd/aaiaaaadsaah aaahd/aaiaaaadsaai aaahd/aaiaaaadsaagaaahd/aaiaaaadsaad aaahd/aaiaaaadsaaeaaahd/aaiaaaadsaajaaahd/aaiaaaadsaakaaahd/aaiaaaadsaalaaahd/aaiaaaadsaamaaahd/aaiaaaadsaanaaahd/aaiaaaadsaaoaaahd/aaiaaaadsaap16rowsselected.sqldeletefromczwhererowidin(selectrow_idfromexceptions);16rows

10、deleted.sqlinsertintoczselectdistinct*fromdups;3rowscreated.sqlselect*fromcz;c1c10c2012dsf23che34dff45err53dar61wee72zxc7rowsselected.从结果里能看到重复记录已删除。数据库中有deny_mobile 表；S31n丁 N Indexei M | M* 湍 |i | Contrainte V &Triggecs口a Scrip惶(GrnteCotumn Name /ID 0Pk HNuP ,Data Type-Default -CLIF NT ID1YVARCHAR2

11、fe)MOBILEr 2YVARCHAR2 (20)createtime匚3ydate甥 wchteDESCRIPTIONr &YVAR CHAR 2 (50)UPDATE一STATUS5YVARCHAR2(1)1需要按照mobile去重复，删除重复记录为：DeletefromSMS_DENYMOBILEWHEREmobileIN(SELECTmobileFROMSMS_DENYMOBILEGROUPBYmobileHAVINGCOUNT(*)1)ANDROWIDNOTIN(SELECTMIN(ROWID)FROMSMS_DENYMOBILEGROUPBYmobileHAVINGCOUNT(*)1)