Oracle如何删除表中重复记录


Oracle如何删除表中重复记录

image

 引言

在对数据库进行操作过程中我们可能会遇到这种情况,表中的数据可能重复出现,使我们对数据库的操作过程中带来读诸多不便,那么怎么删除这些重复没有用的数据呢?

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

 处理过程

重复的数据可能有这样两种情况第一种表中只有某些字段一样,第二种是两行记录完全一样。删除重复记录后的结果也分为2种,第一种是重复的记录全部删除,第二种是重复的记录中只保留最新的一条记录,一般业务中第二种的情况较多。

 

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

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

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

 

2.2  删除部分字段重复数据

2.2.1  重复记录全部删除

想要删除部分字段重复的数据,可以使用下面语句进行删除,下面的语句是删除表中字段1和字段2重复的数据

DELETE FROM 表名 a

WHERE  (字段1, 字段2) 

 IN (SELECT 字段1,字段2 

               FROM   表名

               GROUP  BY 字段1,

                         字段2

               HAVING COUNT(1) > 1)

;

上面的语句非常简单,就是将查询到的数据删除掉。不过这种删除执行的效率非常低,对于大数据量来说,可能会将数据库吊死。所以建议先将查询到的重复的数据插入到一个临时表中,然后进行删除,这样,执行删除的时候就不用再进行一次查询了。如下:

CREATE TABLE 临时表 AS (select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1);

上面这句话就是建立了临时表,并将查询到的数据插入其中。下面就可以进行这样的删除操作了:

delete from 表名 a where 字段1,字段2 in (select 字段1,字段2 from 临时表);

这种先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。

例子:

wps31E3.tmp 

  DELETE FROM tmp_lhr t

  WHERE  (t.accesscode, t.lastserviceordercode, t.serviceinstancecode) IN

         (SELECT  a.accesscode, a.lastserviceordercode, a.serviceinstancecode 

          FROM   tmp_lhr a

          GROUP  BY a.accesscode,

                    a.lastserviceordercode,

                    a.serviceinstancecode

          HAVING COUNT(1) > 1);

wps31E4.tmp 

 

2.2.2  保留最新的一条记录

假如想保留重复数据中最新的一条记录啊!那怎么办呢?在oracle中,有个隐藏了自动rowid,里面给每条记录一个唯一的rowid,我们如果想保留最新的一条记录,我们就可以利用这个字段,保留重复数据中rowid最大的一条记录就可以了。

一、 如何查找重复记录?

SELECT *

  FROM TABLE_NAME A

 WHERE ROWID  NOT IN (SELECT MAX(ROWID)

                   FROM TABLE_NAME D

                  WHERE A.COL1 = D.COL1

                    AND A.COL2 = D.COL2);

二、 如何删除重复记录? 1、 方法1

DELETE FROM TABLE_NAME

WHERE ROWID NOT IN (SELECT MAX(ROWID)

                   FROM TABLE_NAME D

                  group by d.col1,d.col2);

 

这种方法最简单!!!

2、 方法2

DELETE FROM TABLE_NAME A

 WHERE ROWID NOT IN (SELECT MAX(ROWID)

                   FROM TABLE_NAME D

                  WHERE A.COL1 = D.COL1

                    AND A.COL2 = D.COL2);

3、 方法3 临时表

由此,我们要删除重复数据,只保留最新的一条数据,就可以这样写了:

create table 临时表 as select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.字段1,a.字段2;

 

DELETE FROM 正式表 a

where a.rowid NOT IN (SELECT b.dataid

                      FROM   临时表 b

                      WHERE  a.字段1 = b.字段1

   and a.字段2 = b.字段2);

commit;

 

例子:

  DELETE FROM tmp_lhr t

  WHERE  t.rowid not in (SELECT MAX(ROWID)

                     FROM   tmp_lhr a

                     GROUP  BY a.accesscode,

                               a.lastserviceordercode,

                               a.serviceinstancecode);

wps31E5.tmp 

 

 DELETE FROM tmp_lhr t

 WHERE  t.rowid !=

        (SELECT MAX(ROWID)

         FROM   tmp_lhr a

         WHERE  a.accesscode = t.accesscode

         AND    a.lastserviceordercode = t.lastserviceordercode

         AND    a.serviceinstancecode = t.serviceinstancecode);

wps31E6.tmp 

 

2.2.3  删除以某个字段为准的记录

 ----任意保留一条记录

DELETE FROM ods_entity_info_full_lhr_01 T

WHERE  T.ROWID NOT IN (SELECT MAX(A.ROWID)

                       FROM   ods_entity_info_full_lhr_01 A

                       GROUP  BY entity_code,

                                 entity_type); 

        ---保留 entity_id 最大的一条记录

        DELETE FROM ods_entity_info_full_lhr_01 a

        WHERE  a.rowid NOT IN

               (SELECT t.rowid

                FROM   ods_entity_info_full_lhr_01 t

                WHERE  (t.entity_code, t.entity_type, t.entity_id) IN

                       (SELECT entity_code,

                               entity_type,

                               MAX(entity_id)

                        FROM   ods_entity_info_full_lhr_01

                        GROUP  BY entity_code,

                                  entity_type));

2.3  删除完全重复记录

对于表中两行记录完全一样的情况,可以用下面三种方式获取到去掉重复数据后的记录:

1. select distinct * from 表名;

2. select * from 表名 group by 列名1,列名2,... having count(*)>1

3. select * from  表名 a where rowid<(select max(rowid) from 表名 b where a.列名1=b.列名2 and ...)

2.3.1  方法1

  DELETE FROM tmp_lhr t

  WHERE  t.rowid not in (SELECT MAX(ROWID)

                     FROM   tmp_lhr a

                     GROUP  BY a.accesscode,

                               a.lastserviceordercode,

                               a.serviceinstancecode);

2.3.2  方法2

可以将查询的记录放到临时表中,然后再将原来的表记录删除,最后将临时表的数据导回原来的表中。如下:

CREATE TABLE 临时表 AS (select distinct * from 表名);

truncate table 正式表;

insert into 正式表 (select * from 临时表);

drop table 临时表;

 

2.3.3  方法3

DELETE FROM xr_maintainsite E

WHERE  E.ROWID > (SELECT MIN(X.ROWID)

                  FROM   xr_maintainsite X

                  WHERE  X.Maintainid = E.Maintainid

                  AND    x.siteid = e.siteid);--这里被更新表中所有字段都需要写全

 

2.4  采用row_number分析函数取出重复的记录然后删除序号大于1的记录

给出一个例子:

delete from aa where rowid in(select rid from(select rowid rid,row_number() over (partition by name order by id) as seq from aa) where seq>1);

 测试案例

SYS@raclhr1> CREATE TABLE T_ROWS_LHR_20160809 AS SELECT * FROM SCOTT.EMP;

Table created.

SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;

14 rows created.

SYS@raclhr1> COMMIT;

Commit complete.

SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;

28 rows created.

SYS@raclhr1> COMMIT;

Commit complete.

SYS@raclhr1> SELECT COUNT(1) FROM T_ROWS_LHR_20160809;

  COUNT(1)

----------

       

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

外连接转换为内连接的情况 - 2016-08-10 04:08:06

一般的情况下外连接如下a right join b on a.id=b.id 那么b一定要作为驱动表,原因在于只有b作为驱动表才能得到完整的结果集,如果a作为驱动,那么返回的结果集 可能不完整,但是在特殊的情况的,可能将外连接转换为内连接 考虑如下的情况 b    id  name   1   g1   1   g2   2   g3   2   g4 a   id name   2  gname2 使用如下语句: select b.id,a.id from  a right join b on a.id=
今天总算抽了些时间把半自动化的脚本完成了大半,目前还缺少两部分的脚本,一部分是安装前的检查脚本,可以做一个预检查。虽然目前来看还不是必须,但是这些是标准和规范的地方,这些条件不满足,失败的概率会加大。另外一部分是安装后的补充脚本,其实安装后还有很多需要注意的地方。 大体想了下,补充的脚本包含下面的部分。 配置crontab,目前的常用job是定期删除归档,定期检查监听的情况 配置iptables ,把主库的防火墙信息拷贝过来,或者作为静态备份,需要是启用 配置大页,这个可以在优化的基础上进行计算,在内核参数

MongoBD 日常操作小节 - 2016-08-09 17:08:09

一、开启Mongodb 密码验证功能 默认安装完mongodb是不用密码验证的,直接输入mongo就可以登入数据库进行相关操作,设置参数auth=true启动mongodb密码验证功能,开启改功能步骤如下: ①、 修改参数文件auth=false,并重启mongodb ②、登入数据库,创建管理员用户(默认是没有管理员账户的) [ root@mon godb ~]  # mongo user admin   db.createUser(     {       user: "admin",       pw

在Buffer Cache中自动大表缓存 - 2016-08-09 17:08:09

约翰,在A银行做DBA。凯特琳,新的首席技术官,她正在为数据库性能问题由于长的I/O响应时间而沮丧。为了提高性能,她希望增加数据库实例的缓冲区缓存,以便在缓存中缓存更多的数据,从而减少了频繁需要去读磁盘。戴比,系统开发工程师,经历了很多这样的问题在她的职业生涯中,她解释说,“我们的应用有很多全表扫描,数据库的全表扫描使用的是直接路径读取(DPR)在表中的数据块,不做缓冲区高速缓存。”因此,添加物理内存到服务器后,增加缓冲区缓存,是不会帮助,因为缓冲区没有用。应用程序可以通过加hits跳过DPRs,使用缓冲

MySQL案例-mysqld got signal 11 - 2016-08-09 14:08:35

-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------- 背景: MySQL-5.7.12, debian 8核16G虚拟机 , 业务方反馈在某一
今天的技术问答是刘晨兄的一个问题,提问来自于我新书中的一个实验,刘晨兄非常认真,对我书中的很多细节都进行了测试。 看到这个错误,如果出现end-of-file这类的错误信息,基本可以断定数据库实例是宕了。 找到刘晨兄提到的页码标示,原来和我书中的测试结果有一些差别。 我书中的结果类似这样的形式: 错误代码也完全不同,这个问题该怎么解释呢,这个应该是一个很细节的问题。 首先网络上关于这个错误有很多种说法,很多我不认同。 我们先来复现一下问题,找了一套11.2.0.3的环境测试了一下。 先初始化数据 然后复现问
【故障处理】分布式事务 ORA-01591 错误解决 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  分布式事务的简单概念         ②  ORA-01591 错误解决   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lh
我们有几个项目使用了Windows Server 2008+ ROSE HA+Oracle 的组合方案,但是最近遇到了一个不大不小的麻烦。 甲方在进行故障测试时,断掉了一个网络交换机 的电源 (共有两个网络交换机,对应两个冗余的业务网络),Oracle服务竟然停止了,导致所有的客户端数据库连接中断。 首先,这个测试结果甲方是无法接受的; 其次,这个结果也出乎我们的意料,在另外一个交换机正常工作的情况下,ROSE HA 停止了 Oracle服务。 那么,如何给甲方一个交代呢?在和 ROSE售后经过多次沟通后,
故障现象: 用服务器上面的(客户端)sql server management  stutio 去连接本服务器上的sql server 数据库,如下图:点击连接不报任何错,就是连接不上,一直处于等待状态; 但是通过windows 身份验证是可以连接的,并且是可以正常操作的,如下图:服务器名字不在是个IP,要选择local. 通过这个现象可判断出 数据库本身的服务(通过任务管理器中也可以看出来)是没有问题,只是连接方面出现问题:网络,端口或者是监听之类的问题。 这是网上查到的一篇文档:经验证我的这里是 没有
《 Oracle DBA 工作笔记》第二章 常用工具和问题分析   一.1    BLOG 文档结构图     一.2    本文简介 建荣的新书《 Oracle DBA 工作笔记》第二章的目录如下图,主要讲解了 SQL*Plus 、 exp/imp 、 expdp/impdp 以及常见的问题分析,第二章的目录如下:     下边小麦苗将自己阅读完第二章后整理的一些内容分享给大家。 一.3    第一章内容修改 一.3.1    删除数据库的几种方式 这个内容是第一章( http://blog.itpub