度量快速开发平台-专业、快速的软件定制快开平台

标题: Oracle 查找与删除表中重复记录的步骤方法 [打印本页]
作者: 万望    时间: 2020-6-16 22:39
标题: Oracle 查找与删除表中重复记录的步骤方法
案例：一个应用表中的一个字段是主键，向表中插入数据时，先把数据放在临时表中（没有主键）然后再插入应用表。
这时候如果临时表中有重复数据，无论是主键字段businessid有重复，还是一整行有重复都会报出违反唯一主键约束错误。
方法：group by XX having count(*)>1,rowid,distinct,temporary table,procedure
1、查询表中的重复数据
a.重复一个字段
b.重复多个字段
c.重复一整行
创建测试表：
  1. create table cfa (businessid number,customer varchar2(50),branchcode varchar2(10),data_date varchar2(10));
  2. insert into cfa values (1,'Albert','SCB','2011-11-11');
  3. insert into cfa values (2,'Andy','DB','2011-11-12');
  4. insert into cfa values (3,'Allen','HSBC','2011-11-13');
  5. ---------------以下为重复数据----------------------------------------------
  6. insert into cfa values (1,'Alex','ICBC','2011-11-14');
  7. insert into cfa values (1,'Albert','CTBK','2011-11-15');
  8. insert into cfa values (1,'Albert','SCB','2011-11-11');
复制代码
对于a的情况,只有businessid重复
  1. select * from cfa where businessid in (select businessid from cfa group by businessid having count(businessid)>1);
复制代码
如果是b的情况，businessid 和name同时存在重复
  1. select * from cfa where (businessid,customer) in (select businessid,customer from cfa group by businessid,customer having count(*)>1);
复制代码
对于c的情况，重复一整行

参考b的方法：
  1. 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这里不再赘述。
  1. delete from cfa
  2. where businessid in (select businessid
  3. from cfa
  4. group by businessid
  5. having count(businessid) > 1)
  6. and rowid not in (select min(rowid)
  7. from cfa
  8. group by businessid
  9. having count(businessid) > 1);
复制代码
或者，使用下面更简单高效的语句
  1. DELETE FROM cfa t
  2. WHERE t.ROWID >
  3. (SELECT MIN(X.ROWID) FROM cfa X WHERE X.businessid = t.businessid);
复制代码
b情况，删除表中多余的重复记录（多个字段），只留有rowid最小的记录
  1. delete from cfa
  2. where (businessid,customer) in (select businessid,customer
  3. from cfa
  4. group by businessid,customer
  5. having count(*) > 1)
  6. and rowid not in (select min(rowid)
  7. from cfa
  8. group by businessid,customer
  9. having count(*) > 1);
复制代码
或者，使用下面更简单高效的语句
  1. DELETE FROM cfa t
  2. WHERE t.ROWID > (SELECT MIN(X.ROWID)
  3. FROM cfa X
  4. WHERE X.businessid = t.businessid
  5. and x.customer = t.customer);
复制代码
c情况，这种情况就比较简单，使用临时表方法
  1. create table cfabak as select distinct * from cfa;
  2. truncate table cfa;--如果是生产最好对该表backup
  3. Insert into cfa select * from cfabak;
  4. commit;
复制代码


作者: 万望    时间: 2020-6-16 22:40
步骤全给你们贴出咋样
作者: 陈晓龙    时间: 2020-6-17 21:05
有描述，有案例。不错
作者: 陈晓龙    时间: 2020-6-17 21:05




欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://p.delit.cn/) Powered by Discuz! X3.2