DELETE FROM saledata a WHERE ROWID!=(SELECT Max(rowid) FROM saledata WHERE a.salID=salID) ;
--参考这个例子 SQL> select * from test; ID NUM ---------- ---------- 1 1 1 1 2 2 2 2 SQL> delete test a where a.rowid!=(select max(rowid) from test b where a.id=b.id and a.num=b.num);已删除2行。 SQL> commit;提交完成。SQL> select * from test; ID NUM ---------- ---------- 1 1 2 2
数据表saledata中的主码应为salID ---------------------- delete from saledata where rowid in ( select rowid from ( select rowid,row_number() over(partition by salID order by rowid) r1 from saledata ) t where r1 > 1 )
DELETE FROM saledata a WHERE ROWID!=(SELECT Max(rowid) FROM saledata WHERE a.salID=salID) ;
SQL> select * from test; ID NUM
---------- ----------
1 1
1 1
2 2
2 2
SQL> delete test a where a.rowid!=(select max(rowid) from test b where a.id=b.id and a.num=b.num);已删除2行。
SQL> commit;提交完成。SQL> select * from test; ID NUM
---------- ----------
1 1
2 2
----------------------
delete from saledata
where rowid in (
select rowid from (
select rowid,row_number() over(partition by salID order by rowid) r1
from saledata
) t
where r1 > 1
)