oracle之如何删除表中的重复记录只保留其中一条

Answer 1

SQL> select * from t;

ID
----------
1
1
2
2
2
3
4
4

8 rows selected.

SQL> select rowid rid,id from t;

RID ID
------------------ ----------
AAAVveAAHAAAAXFAAA 1
AAAVveAAHAAAAXFAAB 1
AAAVveAAHAAAAXFAAC 2
AAAVveAAHAAAAXFAAD 2
AAAVveAAHAAAAXFAAE 2
AAAVveAAHAAAAXFAAF 3
AAAVveAAHAAAAXFAAG 4
AAAVveAAHAAAAXFAAH 4

8 rows selected.

SQL> delete from t where rowid in (select rowid rid from (select row_number() over (partition by id order by id) rn,id from t) where rn<>1);

4 rows deleted.

SQL> select rowid,id from t;

ROWID ID
------------------ ----------
AAAVveAAHAAAAXFAAA 1
AAAVveAAHAAAAXFAAC 2
AAAVveAAHAAAAXFAAF 3
AAAVveAAHAAAAXFAAG 4

Answer 2

最高效的删除重复记录方法 ( 因为使用了ROWID)
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);