【Oracle】【11】删除重复数据只留一条

正文：

1，根据单个字段（id）查找表中是否有重复记录

select * from table t where t.id in (select t.id from table t group by t.id having count(t.id) > 1);

select * from (select t.id, count(1) count from table t group by t.id) t where t.count != 1;

 

2，根据单个字段（id），删除重复的数据，只留下rowid最小的记录

delete from table t 
where t.id in( select t.id from table t group by t.id having count(t.id) > 1) 
and rowid not in (select min(rowid ) from table t group by t.id having count(t.id) > 1);

 

3，根据多个字段（id, name）查找表中是否有重复记录

select * from table t where (t.id, t.name) in (select t.id, t.name from table t group by t.id, t.name having count(1) > 1)

 

4，根据多个字段（id, name），删除重复的数据，只留下rowid最小的记录

delete from table t 
where (t.id, t.name) in( select t.id, t.name from table t group by t.id, t.name having count(1) > 1) 
and rowid not in (select min(rowid) from table t group by t.id, t.name having count(1) > 1);

 

参考博客：

Oracle 删除重复数据只留一条 - 张曾人 - 博客园
http://www.cnblogs.com/252e/archive/2012/09/13/2682817.html

转载于:https://www.cnblogs.com/huashengweilong/p/10810662.html