Oracle 重复数据查询以及删除
发布时间:2020-12-12 15:20:03 所属栏目:百科 来源:网络整理
导读: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
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) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |