实验表:
create table dupo( id int, name nvarchar2(20), create_time timestamp default sysdate, primary key(id));
实验数据:
insert into dupo(id,name) values(1,'andy'); insert into dupo(id,name) values(2,'andy'); insert into dupo(id,name) values(3,'andy'); insert into dupo(id,name) values(4,'Bill'); insert into dupo(id,name) values(5,'Bill'); insert into dupo(id,name) values(6,'Bill'); insert into dupo(id,name) values(7,'Bill'); insert into dupo(id,name) values(8,'Cindy'); insert into dupo(id,name) values(9,'Cindy'); insert into dupo(id,name) values(10,'Douglas');
由上可见,有三个andy,三个bill,两个cindy是重复了,现在就当它们是人工入力错误,需要用SQL将其删除。
第一种方法,我们可以将dupo变成左右表,删除名称相同而id大于或是小于左表id的记录。
delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id<a.id) delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id>a.id)
上面两句都可以达到目的,但留下的数据id不同,第一条是留最小的,第二条是留最大的。
以上删除如果数据量大,需要给id,name加上联合索引。
第二种方法:利用rowid
delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid<a.rowid) delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid>a.rowid)
与第一种方法比较即可得知,两种方法不同即在于用id还是rowid。
这种删除如果数据量大,只用在name上加单列索引。
执行完后,就可以用下一句把id规整好。
update dupo set id=rownum where 1=1;
以上实验记录:
SQL> create table dupo( 2 id int, 3 name nvarchar2(20), 4 create_time timestamp default sysdate, 5 primary key(id)); 表已创建。 SQL> insert into dupo(id,name) values(1,'andy'); 已创建 1 行。 SQL> insert into dupo(id,name) values(2,'andy'); 已创建 1 行。 SQL> insert into dupo(id,name) values(3,'andy'); 已创建 1 行。 SQL> insert into dupo(id,name) values(4,'Bill'); 已创建 1 行。 SQL> insert into dupo(id,name) values(5,'Bill'); 已创建 1 行。 SQL> insert into dupo(id,name) values(6,'Bill'); 已创建 1 行。 SQL> insert into dupo(id,name) values(7,'Bill'); 已创建 1 行。 SQL> insert into dupo(id,name) values(8,'Cindy'); 已创建 1 行。 SQL> insert into dupo(id,name) values(9,'Cindy'); 已创建 1 行。 SQL> insert into dupo(id,name) values(10,'Douglas'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id<a.id); 已删除6行。 SQL> select id,name from dupo; ID NAME ---------- ---------------------------------------- 1 andy 4 Bill 8 Cindy 10 Douglas SQL> rollback; 回退已完成。 SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.id>a.id); 已删除6行。 SQL> select id,name from dupo; ID NAME ---------- ---------------------------------------- 3 andy 7 Bill 9 Cindy 10 Douglas SQL> rollback; 回退已完成。 SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid<a.rowid); 已删除6行。 SQL> select id,name from dupo; ID NAME ---------- ---------------------------------------- 1 andy 4 Bill 8 Cindy 10 Douglas SQL> rollback; 回退已完成。 SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid>a.rowid); 已删除6行。 SQL> select id,name from dupo; ID NAME ---------- ---------------------------------------- 3 andy 7 Bill 9 Cindy 10 Douglas SQL> rollback; 回退已完成。 SQL> select id,name from dupo; ID NAME ---------- ---------------------------------------- 1 andy 2 andy 3 andy 4 Bill 5 Bill 6 Bill 7 Bill 8 Cindy 9 Cindy 10 Douglas 已选择10行。 SQL> delete from dupo a where exists (select null from dupo b where b.name=a.name and b.rowid>a.rowid); 已删除6行。 SQL> update dupo set id=rownum where 1=1; 已更新4行。 SQL> select id,name from dupo; ID NAME ---------- ---------------------------------------- 1 andy 2 Bill 3 Cindy 4 Douglas SQL>
-END-