删除重复数据

删除重复数据

准备测试表

删除测试表
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<b.rowid);

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

delete test a where exists (select null from test b where a.object_id=b. object_id and a.rowid<b.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);

---