oracle删除重复的行

rrguacoejr

　　需求：当多行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);这种方法更加灵活，可以实现的功能更多。