oracle中用rowid查找和删除重复记录 1文档格式.docx

oracle中用rowid查找和删除重复记录 1文档格式.docx

《oracle中用rowid查找和删除重复记录 1文档格式.docx》由会员分享，可在线阅读，更多相关《oracle中用rowid查找和删除重复记录 1文档格式.docx（13页珍藏版）》请在冰豆网上搜索。

34dff

34dff

45err

53dar

61wee

72zxc

20rowsselected.

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

（1）.SQL>

select*fromczgroupbyc1,c10,c20havingcount（*）>

1;

（2）.SQL>

selectdistinct*fromcz;

（3）.SQL>

select*fromczawhererowid=（selectmax（rowid） 

fromczwherec1=a.c1andc10=a.c10andc20=a.c20）;

C1C10C20

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

12dsf

23che

34dff

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

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

deleteczwhere（c1,c10,c20）in（selectc1,c10,c20fromczgroupbyc1,c10,c20havingcount（*）>

1）androwidnotin（selectmin（rowid）fromczgroupbyc1,c10,c20havingcount（*）>

1）;

deleteczwhererowidnotin（selectmin（rowid）fromczgroupbyc1,c10,c20）;

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

deletefromczawherea.rowid!

=（selectmax（rowid）fromczbwherea.c1=b.c1anda.c10=b.c10anda.c20=b.c20）;

deletefromczawherea.rowid<

（selectmax（rowid）fromczbwherea.c1=b.c1anda.c10=b.c10anda.c20=b.c20）;

deletefromczawhererowid<

（selectmax（rowid）fromczwherec1=a.c1andc10=a.c10andc20=a.c20）;

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

createtabletestasselectdistinct*fromcz;

（建一个临时表test用来存放重复的记录）

truncatetablecz;

（清空cz表的数据，但保留cz表的结构）

insertintoczselect*fromtest;

（再将临时表test里的内容反插回来）

（4）.适用于有大量重复记录的情况（Exceptioninto子句法）：

采用altertable命令中的Exceptioninto子句也可以确定出库表中重复的记录。

这种方法稍微麻烦一些，为了使用“excepeioninto”子句，必须首先创建EXCEPTIONS表。

创建该表的SQL脚本文件为utlexcpt.sql。

对于win2000系统和UNIX系统，Oracle存放该文件的位置稍有不同，在win2000系统下，该脚本文件存放在$ORACLE_HOMEOra90rdbmsadmin目录下；

而对于UNIX系统，该脚本文件存放在$ORACLE_HOME/rdbms/admin目录下

体步骤如下：

@?

/rdbms/admin/utlexcpt.sql

Tablecreated.

SQL>

descexceptions

ROW_IDROWID

OWNERVARCHAR2（30）

TABLE_NAMEVARCHAR2（30）

CONSTRAINTVARCHAR2（30）

altertableczaddconstraintcz_uniqueunique（c1,c10,c20）exceptionsintoexceptions;

ERRORatline1:

ORA-02299:

cannotvalidate（TEST.CZ_UNIQUE）-duplicatekeysfound

createtabledupsasselect*fromczwhererowidin（selectrow_idfromexceptions）;

select*fromdups;

16rowsselected.

selectrow_idfromexceptions;

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

deletefromczwhererowidin（selectrow_idfromexceptions）;

16rowsdeleted.

insertintoczselectdistinct*fromdups;

3rowscreated.

select*fromcz;

45err

53dar

61wee

72zxc

7rowsselected.

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

Oracle查询删除表中重复记录

最高效删除重复记录的方法，使用为例rowid

DELETEFROMEMPEWHEREE.ROWID>

（SELECTMIN（X.ROWID）FROMEMPXWHEREX.EMP_NO=E.EMP_NO）;

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

　　select*frompeoplewherepeopleIdin（select 

peopleIdfrom 

peoplegroupby 

peopleIdhavingcount（peopleId）>

1）

　　2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

　　deletefrompeoplewherepeopleIdin（select 

peopleIdfrompeoplegroupby 

peopleId 

havingcount（peopleId）>

1）androwidnotin（selectmin（rowid）from 

peoplegroupbypeopleIdhavingcount（peopleId）>

1）

　　注:

rowid为oracle自带不用该.....

　　3、查找表中多余的重复记录（多个字段）

　　select*fromvitaeawhere（a.peopleId,a.seq）in 

（selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount（*）>

　　4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

　　deletefromvitaeawhere（a.peopleId,a.seq）in 

1）　androwidnotin（selectmin（rowid）fromvitaegroupbypeopleId,seqhavingcount（*）>

　　5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

　　select*fromvitaea　where（a.peopleId,a.seq）in 

（二）比方说　在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；

　　SelectName,Count（*）FromAGroupByNameHavingCount（*）>

1

　　如果还查性别也相同大则如下:

　　SelectName,sex,Count（*）FromAGroupByName,sexHavingCount（*）>

查询及删除重复记录的SQL语句

select*frompeoplewherepeopleIdin（select 

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

deletefrompeople 

wherepeopleIdin（select 

注:

3、查找表中多余的重复记录（多个字段） 

select*fromvitaeawhere（a.peopleId,a.seq）in 

（selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount（*）>

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

deletefromvitaeawhere（a.peopleId,a.seq）in 

1）androwidnotin（selectmin（rowid）fromvitaegroupbypeopleId,seqhavingcount（*）>

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

1）androwidnotin（selectmin（rowid）fromvitaegroupbypeopleId,seqhavingcount（*）>

（二）

比方说

在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；

SelectName,Count（*）FromAGroupByNameHavingCount（*）>

如果还查性别也相同大则如下:

SelectName,sex,Count（*）FromAGroupByName,sexHavingCount（*）>

declare@maxinteger,@idinteger

declarecur_rowscursorlocalfor

select主字段,count（*）from表名groupby主字段havingcount（*）>

1

opencur_rows

fetchcur_rowsinto@id,@max

while@@fetch_status=0

begin

select@max=@max-1

setrowcount@max

deletefrom表名where主字段=@id

end

closecur_rows

setrowcount0

方法二

＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。

　　1、对于第一种重复，比较容易解决，使用

selectdistinct*fromtableName

　　就可以得到无重复记录的结果集。

　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除

selectdistinct*into#TmpfromtableName

droptabletableName

select*intotableNamefrom#Tmp

droptable#Tmp

　　发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

　　2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下

　　假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

selectidentity（int,1,1）asautoID,*into#TmpfromtableName

selectmin（autoID）asautoIDinto#Tmp2from#TmpgroupbyName,autoID

select*from#TmpwhereautoIDin（selectautoIDfrom#tmp2）

　　最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）

select*fromtablenamewhereidin（

selectidfromtablename

groupbyid

havingcount（id）>

）

Oracle数据库中表的四种连接方式讲解

表的连接是指在一个SQL语句中通过表与表之间的关连,从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析Oracle表的四种不同连接方式:

　　1.相等连接

　　通过两个表具有相同意义的列，可以建立相等连接条件。

　　只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。

　　例查询员工信息以及对应的员工所在的部门信息;

　　SELECT 

* 

FROM 

EMP,DEPT;

EMP,DEPT

　　WHERE 

EMP.DEPTNO 

= 

DEPT.DEPTNO;

　　REM显示工资超过2000的员工信息以及对应的员工的部门名称。

　　2.外连接

　　对于外连接，Oracle中可以使用“（+）”来表示，9i可以使用LEFT/RIGHT/FULLOUTERJOIN，下面将配合实例一一介绍。

除了显示匹配相等连接条件的信息之外,还显示无法匹配相等连接条件的某个表的信息。

　　外连接采用（+）来识别。

　　A）左条件（+）=右条件;

　　代表除了显示匹配相等连接条件的信息之外,还显示右条件所在的表中无法匹配相等连接条件的信息。

　　此时也称为"

右外连接"

.另一种表示方法是:

　　SELECT...FROM表1RIGHTOUTERJOIN表2ON连接条件

　　B）左条件=右条件（+）;

　　代表除了显示匹配相等连接条件的信息之外,还显示左条件所在的表中无法匹配相等连接条件的信息。

左外连接"

.

　　SELECT...FROM表1LEFTOUTERJOIN表2ON连接条件

　　例显示员工信息以及所对应的部门信息

　　--无法显示没有部门的员工信息

　　--无法显示没有员工的部门信息

　　--SELECT 

EMP,DEPT 

WHERE 

　　--直接做相等连接:

EMP 

JOIN 

DEPT 

ON 

　　REM显示员工信息以及所对应的部门信息,显示没有员工的部门信息

EMP.DEPTNO（+） 

RIGHT 

OUTER 

　　REM显示员工信息以及所对应的部门信息,显示没有部门的员工信息

DEPT.DEPTNO（+）;

LEFT 

　　3.不等连接

　　两个表中的相关的两列进行不等连接,比较符号一般为>

<

...,BETWEEN..AND..

　　REM 

SALGRADE

　　--DESC 

SALGRADE;

　　REM显示员工的编号,姓名,工资,以及工资所对应的级别。

EMPNO,ENAME,SAL,SALGRADE.* 

SALGRADE,EMP

EMP.SAL 

BETWEEN 

LOSAL 

AND 

HISAL;

　　REM显示雇员的编号,姓名,工资,工资级别,所在部门的名称;

EMPNO,ENAME,SAL,GRADE,DNAME 

EMP,DEPT,SALGRADE

DEPT.DEPTNO 

　　4.自连接

　　自连接是数据库中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

下面介绍一下自连接的方法:

　　将原表复制一份作为另一个表,两表做笛卡儿相等连接。

　　例显示雇员的编号,名称,以及该雇员的经理名称

　　SELECTWORKER.ENAME,WORKER.MGR,MANAGE