删除表中的重复记录的几种方法

表记录如下：

SQL> select * from t1;

A               B

------ ----------

hh            102

wxc           101

wxc           101

wxc           101

wxc           101

hh            102

hh            102

hh            102

方法一：

create table t2 as (select distinct a,b from t1);

drop table t1;

create table t1 as select * from t2;

方法二：rowid方法

delete from t1 where rowid in (select a.rowid from t1 a,t1 b where a.a = b.a and a.b=b.b and a.rowid > b.rowid);

方法三：max,min函数方法

delete from t1 a where rowid not in(select max(b.rowid) from t1 b where b.a=a.a and b.b=a.b);

delete from t1 a where rowid < (select max(b.rowid) from t1 b where b.a=a.a and b.b=a.b);

delete from t1 a where rowid not in(select min(b.rowid) from t1 b where b.a=a.a and b.b=a.b);

delete from t1 a where rowid > (select min(b.rowid) from t1 b where b.a=a.a and b.b=a.b);

方法四：group by函数方法

delete from t1 where rowid not in(select max(rowid) from t1 group by a,b);

delete from t1 where rowid not in(select min(rowid) from t1 group by a,b);

delete from t1 where (a,b) in (select a,b from t1 group by a,b having(count(*)) > 1) and rowid not in (select max(rowid) from t1 group by a,b having count(*) > 1);

delete from t1 where (a,b) in (select a,b from t1 group by a,b having(count(*)) > 1) and rowid not in (select min(rowid) from t1 group by a,b having count(*) > 1);