Chinaunix首页 | 论坛 | 博客
  • 博客访问: 637357
  • 博文数量: 110
  • 博客积分: 2352
  • 博客等级: 大尉
  • 技术积分: 1253
  • 用 户 组: 普通用户
  • 注册时间: 2012-03-29 14:00
文章分类
文章存档

2018年(1)

2013年(20)

2012年(89)

分类: Oracle

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;

方法二: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) |
给主人留下些什么吧!~~