Question

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

Answer 1

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

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通用

Answer 2

(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

Answer 3

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

Answer 4

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