Oracle 重复数据查询以及删除

算法
算法讲解
tea_year

视频课:https://edu.csdn.net/course/play/7940

Create table test

(id number(2),


 names varchar2(20));


 insert into test values(1,'张三');
 insert into test values(2,'李四');
 insert into test values(3,'马七');

 select * from test;


 --查找重复数据
select * from test a where (a.id,a.names) in

 (select id,names from test group by id,names having count(*) > 1)


 --删除重复数据,只留rowid最小的那行;
 delete from test a where (a.id,a.names) in

 (select id,names from test group by id,names having count(*) > 1)


  and rowid not in (select min(rowid) from test group by id,names having count(*)>1)
  --查找重复数据,不含rowid最小的行
  select * from test a where (a.id,a.names) in
  (select id,names from test group by id,names having count(*) > 1)
  and rowid not in (select min(rowid) from test group by id,names having count(*)>1)

 

---1.以上是重复数据根据多列来判断


以下是重复数据根据单列来判断

1、首先,查找表中多余的重复记录,重复记录是根据单个字段(id)来判断

 

select * from test where id in(select id from test group by having count(id) >1)

 

2、删除表中多余的重复记录,重复记录是根据单个字段(id)来判断,只留有rowid最小的记录

 

delete from test where (id) in (select id from test group by id having count(id) >1) and rowid not in (select min(rowid) from test group by id having count(*)>1)



©️2020 CSDN 皮肤主题: 创作都市 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值