oracle 删除相同的行,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

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

测试：

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

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