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);
或者,使用狼蚁网站SEO优化更简单高效的语句
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);
或者,使用狼蚁网站SEO优化更简单高效的语句
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;
mit;
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南