新建T表如下:
SQL> select * from t;
X Y
---------- --
1 a
1 a
1 a
2 b
2 b
3 a
3 a
1.查询表中重复的记录(在子查询中运用了自连接查出相同记录的max(rowid),通过不等值运算查出去,除了第一条重复记录后的重复记录)
SQL> select x,y from t
2 where rowid!=(select max(rowid) from t a
3 where t.x=a.x
4 and
5 t.y=a.y);
X Y
---------- --
1 a
1 a
2 b
3 a
2.删除重复记录
SQL> delete from t
2 where rowid!=(select max(rowid) from t a
3 where t.x=a.x
4 and
5 t.y=a.y);