17,086
社区成员
发帖
与我相关
我的任务
分享
delete from users where rowid in(select rowid from
(select id,name,rowid, row_number() over(partition by id ,name order by id,name) rid
from users) a
where rid>=2
)
SQL> select * from emp;
ID NAME
---------- --------------------
1001 张三
1002 王五
ID NAME
---------- --------------------
1001 张三
1001 张三
1002 李四
1002 李四
1002 王五
SQL> delete from emp a where rowid < (select max(rowid) from emp b where a.id=b.id);
已删除3行。
SQL> select * from emp;
ID NAME
---------- --------------------
1001 张三
;with args as
(
select id,name,row_number() over(parition by name order by name) Rownumber
from Tab
)
delete from Args
where Rownumber >=2