搜尋
首頁資料庫mysql教程oracle中删除重复的行
oracle中删除重复的行Jun 07, 2016 pm 03:26 PM
oracle刪除試圖遇到重複

平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452 :不能创建唯一索引,发现重复记录。 删除重复记录的方法原理: (1).在Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在Ora

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

删除重复记录的方法原理:
(1).在Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
(2).在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中那些具有最大rowid的就可以了,其余全部删除。

下面总结一下几种查找和删除重复记录的方法(以表CZ为例):
表CZ的结构如下:
SQL> desc cz
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 C1                                                 NUMBER(10)
 C10                                                NUMBER(5)
 C20                                                VARCHAR2(3)

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

经查看表CZ总共有16条记录:
SQL>set pagesize 100
SQL>select * from cz;
         C1        C10 C20
---------- ---------- ---
         1          2   dsf
         1          2   dsf
         1          2   dsf
         1          2   dsf
         2          3   che
         1          2   dsf
         1          2   dsf
         1          2   dsf
         1          2   dsf
         2          3   che
         2          3   che
         2          3   che
         2          3   che
         3          4   dff
         3          4   dff
         3          4   dff
         4          5   err
         5          3   dar
         6          1   wee
         7          2   zxc
20 rows selected.

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
SQL>delete from cz a where rowid
(3).适用于有少量重复记录的情况(临时表法):
SQL>create table test as select distinct * from cz; (建一个临时表test用来存放重复的记录)

SQL>truncate table cz;  (清空cz表的数据,但保留cz表的结构)

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 目录下。

具体步骤如下:
SQL>@?/rdbms/admin/utlexcpt.sql

Table created.

SQL>desc exceptions
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------

 ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                 VARCHAR2(30)
 CONSTRAINT                                   VARCHAR2(30)

SQL>alter table cz add constraint  cz_unique unique(c1,c10,c20) exceptions into exceptions; 
                                                      *
ERROR at line 1:
ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found

SQL>create table dups as select * from cz where rowid in (select row_id from exceptions);

Table created.

SQL>select * from dups;

        C1        C10 C20
---------- ---------- ---
         1          2   dsf
         1          2   dsf
         1          2   dsf
         1          2   dsf
         2          3   che
         1          2   dsf
         1          2   dsf
         1          2   dsf
         1          2   dsf
         2          3   che
         2          3   che
         2          3   che
         2          3   che
         3          4   dff
         3          4   dff
         3          4   dff

16 rows selected.

SQL>select row_id from exceptions;

ROW_ID
------------------
AAAHD/AAIAAAADSAAA
AAAHD/AAIAAAADSAAB
AAAHD/AAIAAAADSAAC
AAAHD/AAIAAAADSAAF
AAAHD/AAIAAAADSAAH
AAAHD/AAIAAAADSAAI
AAAHD/AAIAAAADSAAG
AAAHD/AAIAAAADSAAD
AAAHD/AAIAAAADSAAE
AAAHD/AAIAAAADSAAJ
AAAHD/AAIAAAADSAAK
AAAHD/AAIAAAADSAAL
AAAHD/AAIAAAADSAAM
AAAHD/AAIAAAADSAAN
AAAHD/AAIAAAADSAAO
AAAHD/AAIAAAADSAAP

16 rows selected.

SQL>delete from cz where rowid in ( select row_id from exceptions);

16 rows deleted.

SQL>insert into cz select distinct * from dups;

3 rows created.

SQL>select *from cz;
 
        C1        C10 C20
---------- ---------- ---
         1          2   dsf
         2          3   che
         3          4   dff
         4          5   err
         5          3   dar
         6          1   wee
         7          2   zxc

7 rows selected.

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

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
什么是oracle asm什么是oracle asmApr 18, 2022 pm 04:16 PM

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

oracle怎么查询所有索引oracle怎么查询所有索引May 13, 2022 pm 05:23 PM

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

Oracle怎么查询端口号Oracle怎么查询端口号May 13, 2022 am 10:10 AM

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

oracle全角怎么转半角oracle全角怎么转半角May 13, 2022 pm 03:21 PM

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

oracle怎么删除sequenceoracle怎么删除sequenceMay 13, 2022 pm 03:35 PM

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

oracle怎么查询数据类型oracle怎么查询数据类型May 13, 2022 pm 04:19 PM

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

oracle查询怎么不区分大小写oracle查询怎么不区分大小写May 10, 2022 pm 05:45 PM

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

Oracle怎么修改sessionOracle怎么修改sessionMay 13, 2022 pm 05:06 PM

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

mPDF

mPDF

mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),

SublimeText3 英文版

SublimeText3 英文版

推薦:為Win版本,支援程式碼提示!

Dreamweaver Mac版

Dreamweaver Mac版

視覺化網頁開發工具

Atom編輯器mac版下載

Atom編輯器mac版下載

最受歡迎的的開源編輯器

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境