删除重复数据
准备测试表
删除测试表
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);