Oracle 查找与删除表中重复记录的步骤方法

这时候如果临时表中有重复数据，无论是主键字段businessid有重复，还是一整行有重复都会报出违反唯一主键约束错误。

方法：group by XX having count(*)>1,rowid,distinct,temporary table,procedure

1、查询表中的重复数据
a.重复一个字段

b.重复多个字段

c.重复一整行

创建测试表：

代码如下:

create table cfa (businessid number,customer varchar2(50),branchcode varchar2(10),data_date varchar2(10));
insert into cfa values (1,'Albert','SCB','2011-11-11');
insert into cfa values (2,'Andy','DB','2011-11-12');
insert into cfa values (3,'Allen','HSBC','2011-11-13');

---------------以下为重复数据----------------------------------------------
insert into cfa values (1,'Alex','ICBC','2011-11-14');
insert into cfa values (1,'Albert','CTBK','2011-11-15');
insert into cfa values (1,'Albert','SCB','2011-11-11');

对于a的情况,只有businessid重复

代码如下:

select * from cfa where businessid in (select businessid from cfa group by businessid having count(businessid)>1);

如果是b的情况，businessid 和name同时存在重复


代码如下:

select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);

对于c的情况，重复一整行

参考b的方法：


代码如下:

select * from cfa where (businessid,customer,branchcode,data_date) in (select * from cfa group by businessid,customer,branchcode,data_date having count(*)>1);

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

也可以只保留rowid不是最小记录，需要把代码中的min改为max这里不再赘述。

代码如下:

delete from cfa
where businessid in (select businessid
from cfa
group by businessid
having count(businessid) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid
having count(businessid) > 1);

或者，使用下面更简单高效的语句

代码如下:

DELETE FROM cfa t
WHERE t.ROWID >
(SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);

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

代码如下:

delete from cfa
where (businessid,customer) in (select businessid,customer
from cfa
group by businessid,customer
having count(*) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid,customer
having count(*) > 1);

或者，使用下面更简单高效的语句

代码如下:

DELETE FROM cfa t
WHERE t.ROWID > (SELECT MIN(X.ROWID)
FROM cfa X
WHERE X.businessid = t.businessid
and x.customer = t.customer);

c情况，这种情况就比较简单，使用临时表方法

代码如下:

create table cfabak as select distinct * from cfa;

truncate table cfa;--如果是生产最好对该表backup

Insert into cfa select * from cfabak;

commit;

赞 (0)

相关推荐

  • shell脚本操作oracle删除表空间、创建表空间、删除用户

    oracle下表空间的导出,用户的删除,表空间删除,用户新建,表空间新建,数据导入的shell使用非oracle用户执行该脚本参数说名$1:base表空间的用户名$2:同步表空间的用户名使用场景测试用,base表空间用于升级建立一些固化数据.同步表空间用于测试用,每次去和base表空间拉平数据 复制代码 代码如下: #!/bin/shoraclehome=$ORACLE_HOMEecho $oraclehomelocaldir="/oracle/data"echo $localdir#

  • Oracle删除表及查看表空间的实例详解

    Oracle常用的基本命令 --1.用户下表中注释模糊查询: 例如查询与优惠券关联的表 SELECT * FROM user_tab_comments t WHERE t.comments LIKE '%优惠券%'; ![这里写图片描述](http://img.blog.csdn.net/20170321112728053?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMDQyNzkzNQ==/font/5a6L5L2T/fontsize/40

  • Oracle 查看表空间的大小及使用情况sql语句

    SQL1: 复制代码 代码如下: --1.查看表空间的名称及大小 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; --2.查看表空间物理文件的名称及大小 SELECT tables

  • oracle删除表字段和oracle表增加字段

    添加字段的语法:alter table tablename add (column datatype [default value][null/not null],-.); 修改字段的语法:alter table tablename modify (column datatype [default value][null/not null],-.); 删除字段的语法:alter table tablename drop (column); 添加.修改.删除多列的话,用逗号隔开. 使用alter

  • Oracle删除表前判断表名是否存在若存在则删除

    在Oracle中若删除一个不存在的表,如 "DROP TABLE notExistTable",则会提示: ORA-00942:表或视图不存在, 若使用程序执行该语句则会报异常,这就需要我们再删除表前判断该表是否存在,若存在则删除. 下面是不使用存储过程实现删除表的SQL: 复制代码 代码如下: <span style="font-family:Times New Roman;font-size:18px;">DECLARE num NUMBER; BE

  • oracle查看表空间已分配和未分配空间的语句分享

    复制代码 代码如下: selectb.tablespace_name,mbytes_alloc,mbytes_free from(select round(sum(bytes)/1024/1024) mbytes_free,tablespace_name from dba_free_space group by tablespace_name) a, (select round(sum(bytes)/1024/1024) mbytes_alloc,tablespace_name from dba

  • Oracle误删除表数据后的数据恢复详解

    Oracle误删除表数据后的恢复详解   测试环境: SYSTEM:IBM AIX 5L                         Oracle Version:10gR2 1. undo_retention参数的查询与修改 使用show parameter undo命令查看当前的数据库参数undo_retention设置. 显示如下: SQL> show parameter undo NAME                                 TYPE        VAL

  • Oracle 查找与删除表中重复记录的步骤方法

    这时候如果临时表中有重复数据,无论是主键字段businessid有重复,还是一整行有重复都会报出违反唯一主键约束错误. 方法:group by XX having count(*)>1,rowid,distinct,temporary table,procedure 1.查询表中的重复数据a.重复一个字段 b.重复多个字段 c.重复一整行 创建测试表: 复制代码 代码如下: create table cfa (businessid number,customer varchar2(50),bra

  • Oracle表中重复数据去重的方法实例详解

    Oracle表中重复数据去重的方法实例详解 我们在项目中肯定会遇到一种情况,就是表中没有主键 有重复数据 或者有主键 但是部分字段有重复数据 而我们需要过滤掉重复数据 下面是一种解决方法 delete from mytest ms where rowid in (select aa.rid from (select rowid as rid, row_number() over(partition by s.name order by s.id) as nu from mytest s) aa

  • python删除列表中重复记录的方法

    本文实例讲述了python删除列表中重复记录的方法.分享给大家供大家参考.具体实现方法如下: def removeListDuplicates(seq): seen = set() seen_add = seen.add return [ x for x in seq if x not in seen and not seen_add(x) ] 希望本文所述对大家的Python程序设计有所帮助.

  • mysql查找删除表中重复数据方法总结

    在数据库表里,我们有时候会保存了很多重复的数据,这些重复的数据浪费资源,我们要将其删除掉,应该怎么处理呢?下面来看一下. 先看下我们的表数据,有一些数据是重复的. 要查找重复数据,我们可以使用mysql里的having语句,如图. 执行这个语句后,我们可以看到现在的结果里显示的就是表中重复数据的字段. 要删除这些重复的数据,我们找出这些数据的ID,在select语句里,添加id字段,使用max函数,可以得到重复数据最后面的id. 执行结果如图,得到重复数据的id为8和9. 这样我们就可以使用de

  • Sql Server里删除数据表中重复记录的例子

    ［项目］ 数据库中users表,包含u_name,u_pwd两个字段,其中u_name存在重复项,现在要实现把重复的项删除! ［分析］ 1.生成一张临时表new_users,表结构与users表一样: 2.对users表按id做一个循环,每从users表中读出一个条记录,判断new_users中是否存在有相同的u_name,如果没有,则把它插入新表:如果已经有了相同的项,则忽略此条记录: 3.把users表改为其它的名称,把new_users表改名为users,实现我们的需要. ［程序］ 复制代

  • MySql删除表中一行的实操方法

    MySql删除表中一行的实操方法

    首先你要确定能够唯一确定你那一行数据的字段或字段组合是哪些, DELETE FROM 表名 WHERE 字段1 = '' and 字段2 = '' and ...字段1,...为能够唯一确定某一行数据的字段组合,''中填写你要 删除的字段具体值就可以了 如果有主键,则直接利用主键确定某一行就可以了. DELETE FROM 表名 WHERE 主键 = '具体值'. delete from ms_cf01 where brxm='张三' and id='7598'; 其中: ms_cf01 为你所

  • SQL Server删除表中的重复数据

    SQL Server删除表中的重复数据

    添加示例数据 create table Student( ID varchar(10) not null, Name varchar(10) not null, ); insert into Student values('1', 'zhangs'); insert into Student values('2', 'zhangs'); insert into Student values('3', 'lisi'); insert into Student values('4', 'lisi')

  • sql 删除表中的重复记录

    遇见了表中存在重复的记录的问题,直接写sql删除时最快的,才不要慢慢的复制到excel表中慢慢的人工找呢 如下sql,找出重复的记录,和重复记录中ID值最小的记录(表中ID为自增长) select MIN(ID) as id, StructSN ,Date,UserID,StarCount,COUNT(StructSN) as c from T_Dor_StructStar where Date >= '20160919' group by StructSN ,Date,UserID,StarC

  • ORACLE查询删除重复记录三种方法

    比如现在有一人员表 (表名:peosons) 若想将姓名.身份证号.住址这三个字段完全相同的记录查询出来 复制代码 代码如下: select p1.*   from persons  p1,persons  p2   where p1.id<>p2.id   and  p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address 可以实现上述效果． 几个删除重复记录的SQL语句 1.用rowid方法 2.用g

  • mysql删除重复记录语句的方法

    例如: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id是主键 要求得到这样的结果 id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii 方法1 delete YourTable where [id] not in ( select max([id]) from YourTable group by (name + value)) 方法2 delet

随机推荐