oracle去重复值查询,Oracle 重复数据查询以及删除

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 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 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)

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。