SQL> create table test( 2 id int, 3 num int);表已创建。SQL> insert into test values(1,1);已创建 1 行。SQL> insert into test values(1,1);已创建 1 行。SQL> insert into test values(2,2);已创建 1 行。SQL> insert into test values(2,2);已创建 1 行。SQL> commit;提交完成。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
补充一下 我userid没有唯一约束
我做测试 的表也什么约束都么有啊,根据rowid来做的
delete from user a where a.rowid <(select max(rowid) from user b where a.userid=b.userid having count(1)>=2 group by b.userid)
--删除重复记录的3种方法 1.delete from user t where t.rowid > (select min(x.rowid) from user x where t.userid = x.userid); 2.delete from user t where t.rowid <> (select min(x.rowid) from user x where t.userid = x.userid); 3.delete from user where rowid in(select rd from(select rowid rd,row_number()over(partition by userid order by 1 )rn from user ) where rn <>1 );
rowid比较灵活,还可以使用:1.delete from user t where t.rowid < (select max(x.rowid) from user x where t.userid = x.userid); 2.delete from user t where t.rowid <> (select max(x.rowid) from user x where t.userid = x.userid); 答案是一样的,都是删除重复记录!
2 id int,
3 num int);表已创建。SQL> insert into test values(1,1);已创建 1 行。SQL> insert into test values(1,1);已创建 1 行。SQL> insert into test values(2,2);已创建 1 行。SQL> insert into test values(2,2);已创建 1 行。SQL> commit;提交完成。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
--删除重复记录的3种方法
1.delete from user t where t.rowid > (select min(x.rowid) from user x where t.userid = x.userid);
2.delete from user t where t.rowid <> (select min(x.rowid) from user x where t.userid = x.userid);
3.delete from user
where rowid in(select rd
from(select rowid rd,row_number()over(partition by userid order by 1 )rn
from user
)
where rn <>1
);
2.delete from user t where t.rowid <> (select max(x.rowid) from user x where t.userid = x.userid); 答案是一样的,都是删除重复记录!