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

第一种：新建表，需停业务

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);