【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