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

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