• 博客访问： 514159
• 博文数量： 110
• 博客积分： 2352
• 博客等级： 大尉
• 技术积分： 1253
• 用 户 组： 普通用户
• 注册时间： 2012-03-29 14:00

2018年（1）

2013年（20）

2012年（89）

2012-09-07 10:39:48

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;

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

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

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