删除表中重复数据的四种方法（oracle）

2019独角兽企业重金招聘Python工程师标准>>>

第一种：新建表，需停业务
select distinct * from t2;
create table t3 as select * from t2;
create table tmp_t3 as select distinct * from t3;
select * from tmp_t3;
drop table t3;
alter table tmp_t3 rename to t3;
select * from t3;

第二种：用rowid
select rowid,c1,c2 from t2;
delete from t2
    where rowid <> ( select min(rowid)
                     from t2 b
                     where b.c1 = t2.c1
                       and b.c2 = t2.c2 )；

第三种：用rowid + group by
delete from T2
    where rowid not in (select min(rowid)
    from t2 group by c1,c2 );

--rowid>最小的
delete from t2
    where rowid > ( select min(rowid)
                     from t2 b
                     where b.c1 = t2.c1
                       and b.c2 = t2.c2 )
-- not exists
delete from t2 
where not exists (select 1 from (select min(rowid) rid from t2 group by c1,c2) b where b.rid=t2.rowid);

第四种：用分析函数
select c1,c2,rowid rd,row_number() over(partition by c1,c2 order by c1) rn from t2;

select b.c1,b.c2 from 
    (select c1,c2,rowid rd,row_number() over(partition by c1,c2 order by c1) rn 
     from t2) b 
where b.rn = 1；

delete from t2 where rowid in 
(select b.rd from 
(select rowid rd,row_number() over(partition by c1,c2 order by c1) rn 
from t2) b 
where b.rn > 1);
或者
delete from t2 where rowid not in 
(select b.rd from 
(select rowid rd,row_number() over(partition by c1,c2 order by c1) rn 
from t2) b 
where b.rn = 1);

转载于:https://my.oschina.net/u/3705048/blog/1557563