oracle中如何删除一个表中的重复数据,如删除表a中字段b重复的数据.有3条重复删除两条保留一条

有没有唯一列如果有的情况下

delete b from a as b where exists(select 1 from a where b=b.b and ID>b.ID)--保留最大
delete b from a as b where exists(select 1 from a where b=b.b and ID<b.ID)--保留最小

其它方法参照整理贴，多数方法都可在oracle通用

参考资料：http://blog.csdn.net/roy_88/article/details/2715965

本回答被提问者和网友采纳

(1).适用于有大量重复记录的情况(列上建有索引的时候，用以下语句效率会很高)：
Delete empa Where empno In (Select empno From empa Group By empno Having Count(*) > 1)
And ROWID Not In (Select Min(ROWID) From empa Group By empno Having Count(*) > 1);

Delete empa Where ROWID Not In(Select Min(ROWID) From empa Group By empno);

(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)：
Delete empa a where rowid<>(select max(rowid) from empa where empno=a.empno

delete from a table1
where table1.rowid<(select max(table2.rowid) from a table2 where table1.b=table2.b);

delete from a where rowid not in (select min(rowid) from a group by b)