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
升级会员 