oracle中查找和删除重复记录的几种方法总结

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

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

表CZ的结构如下:

1 SQL> desc cz   
2   Name Null? Type   
3   -------------------------------------------------------------------   
4   C1 NUMBER(10)   
5   C10 NUMBER(5)   
6   C20 VARCHAR2(3) 

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

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

 

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

 

重复记录判断的标准是:

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

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

 1 SQL>set pagesize 100   
 2   SQL>select * from cz;   
 3   C1 C10 C20   
 4   ---------- ---------- ---   
 5   1 2 dsf   
 6   1 2 dsf   
 7   1 2 dsf   
 8   1 2 dsf   
 9   2 3 che   
10   1 2 dsf   
11   1 2 dsf   
12   1 2 dsf   
13   1 2 dsf   
14   2 3 che   
15   2 3 che   
16   2 3 che   
17   2 3 che   
18   3 4 dff   
19   3 4 dff   
20   3 4 dff   
21   4 5 err   
22   5 3 dar   
23   6 1 wee   
24   7 2 zxc   
25   20 rows selected. 

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

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

 

1 SQL>select * from cz group by c1,c10,c20 having count(*) >1;
2 
3 C1 C10 C20   
4 -----------------------   
5 1 2 dsf   
6 2 3 che   
7 3 4 dff 

 

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

 

1 SQL>select distinct * from cz;
2 
3 C1 C10 C20   
4 -----------------------   
5 1 2 dsf   
6 2 3 che   
7 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);

 

1 SQL>select * from cz a where rowid=(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
2 
3 C1 C10 C20   
4 -----------------------   
5 1 2 dsf   
6 2 3 che   
7 3 4 dff 

 

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

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

 

 1 SQL>DELETE FROM cz
 2       WHERE (c1, c10, c20) IN (  SELECT c1, c10, c20
 3                                    FROM cz
 4                                GROUP BY c1, c10, c20
 5                                  HAVING COUNT (*) > 1)
 6             AND ROWID NOT IN (  SELECT MIN (ROWID)
 7                                   FROM cz
 8                               GROUP BY c1, c10, c20
 9                                 HAVING COUNT (*) > 1);
10 
11 SQL>DELETE fron cz
12  WHERE ROWID NOT IN (  SELECT MIN (ROWID)
13                          FROM cz
14                      GROUP BY c1, c10, c20);

 

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

 

 1 SQL>DELETE FROM cz a
 2       WHERE a.ROWID !=
 3                (SELECT MAX (ROWID)
 4                   FROM cz b
 5                  WHERE a.c1 = b.c1 AND a.c10 = b.c10 AND a.c20 = b.c20);
 6 
 7 SQL>DELETE FROM cz a
 8       WHERE a.ROWID <
 9                (SELECT MAX (ROWID)
10                   FROM cz b
11                  WHERE a.c1 = b.c1 AND a.c10 = b.c10 AND a.c20 = b.c20);
12 
13 SQL>DELETE FROM cz a
14       WHERE ROWID < (SELECT MAX (ROWID)
15                        FROM cz
16                       WHERE c1 = a.c1 AND c10 = a.c10 AND c20 = a.c20);

 

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

 

1 SQL>create table test as select distinct * from cz; (建一个临时表test用来存放重复的记录)
2 
3 SQL>truncate table cz; (清空cz表的数据,但保留cz表的结构)
4 
5 SQL>insert into cz select * from test; (再将临时表test里的内容反插回来)

 

(4).适用于有大量重复记录的情况(Exception into 子句法):

采用alter table 命令中的 Exception into 子句也可以确定出库表中重复的记录。这种方法稍微麻烦一些,为了使用“excepeion into ”子句,必须首先创建 EXCEPTIONS 表。创建该表的 SQL 脚本文件为 utlexcpt.sql 。对于win2000系统和 UNIX 系统, Oracle 存放该文件的位置稍有不同,在win2000系统下,该脚本文件存放在$ORACLE_HOME\Ora90\rdbms\admin 目录下;而对于 UNIX 系统,该脚本文件存放在$ORACLE_HOME/rdbms/admin 目录下。

具体步骤如下:

 

 

oracle中查找和删除重复记录的几种方法总结
 1 SQL>@?/rdbms/admin/utlexcpt.sql
 2     Table created.
 3     SQL>desc exceptions
 4     Name Null? Type
 5     ----------------------------------------- -------- --------------
 6     ROW_ID ROWID
 7     OWNER VARCHAR2(30)
 8     TABLE_NAME VARCHAR2(30)
 9     CONSTRAINT VARCHAR2(30)
10     SQL>alter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into exceptions;
11     *
12     ERROR at line 1:
13     ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found
14     SQL>create table dups as select * from cz where rowid in (select row_id from exceptions);
15     Table created.
16     SQL>select * from dups;
17     C1 C10 C20
18     ---------- ---------- ---
19 2 dsf
20 2 dsf
21 2 dsf
22 2 dsf
23 3 che
24 2 dsf
25 2 dsf
26 2 dsf
27 2 dsf
28 3 che
29 3 che
30 3 che
31 3 che
32 4 dff
33 4 dff
34 4 dff
35 rows selected.
36     SQL>select row_id from exceptions;
37     ROW_ID
38     ---------- ---------- ---
39     AAAHD/AAIAAAADSAAA
40     AAAHD/AAIAAAADSAAB
41     AAAHD/AAIAAAADSAAC
42     AAAHD/AAIAAAADSAAF
43     AAAHD/AAIAAAADSAAH
44     AAAHD/AAIAAAADSAAI
45     AAAHD/AAIAAAADSAAG
46     AAAHD/AAIAAAADSAAD
47     AAAHD/AAIAAAADSAAE
48     AAAHD/AAIAAAADSAAJ
49     AAAHD/AAIAAAADSAAK
50     AAAHD/AAIAAAADSAAL
51     AAAHD/AAIAAAADSAAM
52     AAAHD/AAIAAAADSAAN
53     AAAHD/AAIAAAADSAAO
54     AAAHD/AAIAAAADSAAP
55 rows selected.
56     SQL>delete from cz where rowid in ( select row_id from exceptions);
57 rows deleted.
58     SQL>insert into cz select distinct * from dups;
59 rows created.
60     SQL>select *from cz;
61     C1 C10 C20
62     ---------- ---------- ---
63 2 dsf
64 3 che
65 4 dff
66 5 err
67 3 dar
68 1 wee
69 2 zxc
70 rows selected.
View Code

 

从结果里可以看到重复记录已经删除。

原文链接:http://database.ctocio.com.cn/457/9486457_3.shtml

oracle中查找和删除重复记录的几种方法总结

上一篇:FIREDAC连接MSSQL 2000报不能支持连接MSSQL2000及更低版本的解决办法


下一篇:react-native Android 全面屏手机 底部留有一大块黑屏