Oracle 查找与删除表中重复记录的步骤技巧
发布时间：2021-12-03 18:59:55 所属栏目：教程 来源：互联网
导读：这时候如果临时表中有重复数据，无论是主键字段businessid有重复，还是一整行有重复都会报出违反唯一主键约束错误。 方法：group by XX having count(*)1,rowid,distinct,temporary table,procedure 1、查询表中的重复数据 a.重复一个字段 b.重复多个字段 c.
|
这时候如果临时表中有重复数据，无论是主键字段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;  （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号