表记录如下:
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);
阅读(1858) | 评论(0) | 转发(1) |