删除表中的重复行,首先重复出现的行必须满足的条件是与其他行的值相同,另外还需要判断空行,再进一步进行处理
表结构
SQL>desc test1
Name Null? Type
---------------------------------------------------
ID NUMBER
DEPARTMENT VARCHAR2(33)
如下共有11行数据 第3,5行和第7,9行数据相同 现在删除重复的行
SQL> select * from test1;
ID DEPARTMENT
---------- ---------------------------------
5 afa啊打发add阿发afda
6 啊发发afda阿佛奥
1 111
3 售后
1 111
7 ddddd
9
8 小的
9
4 afa阿凡达
10 afafa
11 rows selected.
SQL>
如下只删除了id=1 没有删除id=9的重复行
SQL> delete test1 a
2 where a.rowid >
3 (select min(rowid) from test1 b
4 where b.id=a.id and b.department=a.department);
1 row deleted.
SQL> select * from test1;
ID DEPARTMENT
---------- ---------------------------------
5 afa啊打发add阿发afda
6 啊发发afda阿佛奥
1 111
3 售后
7 ddddd
9
8 小的
9
4 afa阿凡达
10 afafa
10 rows selected.
SQL>
rollback回滚
SQL> rollback;
Rollback complete.
SQL> select * from test1;
ID DEPARTMENT
---------- ---------------------------------
5 afa啊打发add阿发afda
6 啊发发afda阿佛奥
1 111
3 售后
1 111
7 ddddd
9
8 小的
9
4 afa阿凡达
10 afafa
11 rows selected.
SQL>
当值为null 上面的删除用=无效 需要另作判断
SQL> delete test1 a where a.rowid
2 > (select min(rowid)
3 from test1 b
4 where ((b.id=a.id)or(b.id is null and a.id is null))
5 and ((b.department=a.department)or(b.department is null and a.department is null)));
2 rows deleted.
SQL> select * from test1;
ID DEPARTMENT
---------- ---------------------------------
5 afa啊打发add阿发afda
6 啊发发afda阿佛奥
1 111
3 售后
7 ddddd
9
8 小的
4 afa阿凡达
10 afafa
9 rows selected.
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27013009/viewspace-1225688/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27013009/viewspace-1225688/