• 售前

  • 售后

热门帖子
入门百科

Oracle 查找与删除表中重复记载的步调方法

[复制链接]
哈哈笑417 显示全部楼层 发表于 2021-10-26 13:19:45 |阅读模式 打印 上一主题 下一主题
这时间假如临时表中有重复数据,无论是主键字段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;

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作