T@ora>create table a(c int);Table created.Elapsed: 00:00:00.01 T@ora>insert into a values(1);1 row created.Elapsed: 00:00:00.01 T@ora>insert into a values(1);1 row created.Elapsed: 00:00:00.00 T@ora>insert into a values(1);1 row created.Elapsed: 00:00:00.01 T@ora>insert into a values(2);1 row created.Elapsed: 00:00:00.00 T@ora>delete from a where rowid <> (select max(rowid) from a aa where a.c = aa.c);2 rows deleted.Elapsed: 00:00:00.01 T@ora>select * from a; C ---------- 1 2 第2种方法 T@ora>delete from a where rowid in(select rowid from (select rowid,row_number() over (partition by c order by c) rn from a) where rn <>1 );
delete * from a where rowid !=(select max(rowid) from b where b.xx = a.xx)
T@ora>insert into a values(1);1 row created.Elapsed: 00:00:00.01
T@ora>insert into a values(1);1 row created.Elapsed: 00:00:00.00
T@ora>insert into a values(1);1 row created.Elapsed: 00:00:00.01
T@ora>insert into a values(2);1 row created.Elapsed: 00:00:00.00
T@ora>delete from a where rowid <> (select max(rowid) from a aa where a.c = aa.c);2 rows deleted.Elapsed: 00:00:00.01
T@ora>select * from a; C
----------
1
2
第2种方法
T@ora>delete from a where rowid in(select rowid from (select rowid,row_number() over (partition by c order by c) rn from a) where rn <>1 );
where rowid !=(select max(rowid) from b where b.xx = a.xx)