dba sql 数据库 删除重复数据

删除重复数据

准备测试表

删除测试表 drop table test;

create table test as select * from dba_objects;

insert into test select * from test;

进行删除操作

方法一 delete from test where rowid not in (select max(rowid) from test group by object_id);

方法二

delete test a where a.object_id in (select b.object_id from test b where a.object_id =b.object_id and a.rowid

方法三 执行效率很慢的一种方式 速度比in快

delete test a where exists (select null from test b where a.object_id=b. object_id and a.rowid

方法三

根据oracle数据库中rowid进行删除 delete test where rowid in ( select rowid from (select row_number() over(partition by object_id order by rowid) rn,a.* from test a) where rn>1);

为了方便可以

create table test_tmp as select distinct * from test; truncate table test; insert into test select * from test_tmp; drop table test_tmp ; 为了保留原表相关信息，但是效率差，容易卡死。但是方便。

全字段重复和除了主键剩下都重复两种情况，第二种可以分局主键进行确认是否删除，那全字段重复没有主键或是rowid伪列进行确认怎么删除？

gp数据库 DELETE FROM test WHERE (gp_segment_id, ctid) NOT IN (SELECT gp_segment_id, min(ctid) FROM test GROUP BY city, gp_segment_id);

相关链接

 您阅读本篇文章共花了：