oracle删除重复的行

轮回稻草人

浏览: 28938 次

最近访客更多访客>>

richardyang123

xuan108

zhoufh

一丝冰凉

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

oracle

需求：当多行tid重复时，保留tid小的的行，其他重复的行删除。

原书数据：
select t.*,t.rowid from test12 t;
TID TNAME TTIME SSEX ROWID
11 12               AAAMP+AAEAAAAC0AAA
111 1a2          AAAMP+AAEAAAAC0AAB
21 2121             2012-11-13 15:30:45 AAAMP+AAEAAAAC0AAC
21 2121             2012-11-13 15:41:18 男 AAAMP+AAEAAAAC0AAD
23 2121             2012-11-13 15:41:31 女 AAAMP+AAEAAAAC0AAE
23 2121             2012-11-13 15:41:48 nv AAAMP+AAEAAAAC0AAF

实现：通过rowid
select s.tid,min(s.rowid) from test12 s,test12 t where t.tid=s.tid group by s.tid;

11 AAAMP+AAEAAAAC0AAA
21 AAAMP+AAEAAAAC0AAC
23 AAAMP+AAEAAAAC0AAE
111 AAAMP+AAEAAAAC0AAB

select s.tid,s.rowid from test12 s,test12 t where s.rowid>t.rowid and t.tid=s.tid;
21 AAAMP+AAEAAAAC0AAD
23 AAAMP+AAEAAAAC0AAF

select s.tid,s.rowid from test12 s,test12 t where s.rowid<t.rowid and t.tid=s.tid;
21 AAAMP+AAEAAAAC0AAC
23 AAAMP+AAEAAAAC0AAE

删除方法一：成功！
delete test12 t where t.rowid not in (select min(s.rowid) from test12 s where t.tid=s.tid group by s.tid);----如果是保留最大的唯一行，换max即可。
TID TNAME TTIME SSEX ROWID
11 12               AAAMP+AAEAAAAC0AAA
111 1a2          AAAMP+AAEAAAAC0AAB
21 2121             2012-11-13 15:30:45 AAAMP+AAEAAAAC0AAC
23 2121             2012-11-13 15:41:31 女 AAAMP+AAEAAAAC0AAE

删除方法二：失败！
delete test12 t where t.rowid in (select s.rowid from test12 s where s.tid=t.tid and s.rowid>t.rowid);

解：
由于这个例子的特殊性，所以select s.tid,s.rowid from test12 s,test12 t where s.rowid>t.rowid and t.tid=s.tid;
和
select s.tid,s.rowid from test12 s,test12 t where s.rowid<t.rowid and t.tid=s.tid;可以查询出来正确的结果。

测试：
insert into test12 values(23,'fsf',sysdate,'F');
insert into test12 values(23,'fsf',sysdate,'M');

select t.*,t.rowid from test12 t;
TID TNAME TTIME SSEX ROWID
11 12               AAAMP+AAEAAAAC0AAA
111 1a2          AAAMP+AAEAAAAC0AAB
21 2121             2012-11-13 15:30:45 AAAMP+AAEAAAAC0AAC
21 2121             2012-11-13 15:41:18 男 AAAMP+AAEAAAAC0AAD
23 2121             2012-11-13 15:41:31 女 AAAMP+AAEAAAAC0AAE
23 2121             2012-11-13 15:41:48 nv AAAMP+AAEAAAAC0AAF
23 fsf          2012-11-14 10:09:14 F AAAMP+AAEAAAAC2AAA
23 fsf          2012-11-14 10:11:50 M AAAMP+AAEAAAAC2AAB

错误结果一：
select s.tid,s.rowid from test12 s,test12 t where s.rowid>t.rowid and t.tid=s.tid;
21 AAAMP+AAEAAAAC0AAD
23 AAAMP+AAEAAAAC2AAB
23 AAAMP+AAEAAAAC2AAA
23 AAAMP+AAEAAAAC0AAF
23 AAAMP+AAEAAAAC2AAB
23 AAAMP+AAEAAAAC2AAA
23 AAAMP+AAEAAAAC2AAB

错误结果二：
select s.tid,s.rowid from test12 s,test12 t where s.rowid<t.rowid and t.tid=s.tid;
21 AAAMP+AAEAAAAC0AAC
23 AAAMP+AAEAAAAC0AAE
23 AAAMP+AAEAAAAC0AAF
23 AAAMP+AAEAAAAC0AAE
23 AAAMP+AAEAAAAC2AAA
23 AAAMP+AAEAAAAC0AAF
23 AAAMP+AAEAAAAC0AAE

由于是比较，两个表，以笛卡尔积的方式，进行对比，取小的rowid（错误二的例子），首选s表的第一行（tid为23第一行）AAAMP+AAEAAAAC0AAE
与t表的第一比较，相等，无结果；与第二行，第三行，第四行（tid为23的四行），均最小，则，有三条结果；然后s表的第二行开始比较，比t表的第一行大，与第二行相等，比第三、四行相等，则有两条结果；然后s表的第三行，再去比较，比t表的第一、二行都大，与第三行相等，比第四行小，所以一条结果；s表第四行比t表第一、二、三行都大，与第四行相等，所以无结果。综上所述，最终结果（无序的）为：
21 AAAMP+AAEAAAAC0AAC
23 AAAMP+AAEAAAAC0AAE
23 AAAMP+AAEAAAAC0AAF
23 AAAMP+AAEAAAAC0AAE
23 AAAMP+AAEAAAAC2AAA
23 AAAMP+AAEAAAAC0AAF
23 AAAMP+AAEAAAAC0AAE

补充：如果要指定删除重复(tid)行为4（N）行以上的，保留最小的行，其他的删除。
则在where后增加rowid not in (select min(rowid) from test12 group by tid having count(*) >4) and tid in (select tid from test12 group by tid having count(*) >4);这种方法更加灵活，可以实现的功能更多。