查询和删除表中重复记录

论坛里经常有人发问关于如何删除表中重复记录的帖子,自己以前也就是进去看一看其他人的回答而已,今天正好想起来这个事,就自己动手做做实验,加强一下印象吧.

 

1.使用ROWID的方法

SQL> create table nn(id number,name number);

Table created.

SQL> insert into nn values(1,1);

1 row created.

SQL> insert into nn values(1,1);

1 row created.

SQL> insert into nn values(1,2);

1 row created.

SQL> insert into nn values(1,3);

1 row created.

SQL> insert into nn values(2,3);

1 row created.

SQL> insert into nn values(3,3);

1 row created.

SQL> insert into nn values(3,3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from nn;

        ID       NAME
---------- ----------
         1          1
         1          1   --重复记录
         1          2
         1          3
         2          3
         3          3
         3          3   --重复记录

7 rows selected.

SQL> select * from nn a where rowid!=(select max(rowid)
  2  from nn b where a.id=b.id and a.name=b.name);

        ID       NAME
---------- ----------
         1          1
         3          3                 --查询出重复的记录

SQL> delete from nn a where rowid!=(select max(rowid)
  2  from nn b where a.id=b.id and a.name=b.name);

2 rows deleted.               --删除多余重复记录(保留一条)成功

SQL> select * from nn;

        ID       NAME
---------- ----------
         1          1
         1          2
         1          3
         2          3
         3          3

SQL> rollback;

Rollback complete.

SQL> select * from nn;

        ID       NAME
---------- ----------
         1          1
         1          1
         1          2
         1          3
         2          3
         3          3
         3          3

7 rows selected.

 

2.使用GROUP BY的方法

SQL> select id,name from nn
  2  group by id,name
  3  having count(*) > 1;

        ID       NAME
---------- ----------
         1          1
         3          3               --查询出重复记录

SQL>

SQL> delete from nn
  2  where (id,name) in (select id,name from (select count(*),id,name from nn gr
oup by id,name having count(*) > 1));

4 rows deleted.                --这里把重复记录全部删除(而不是删除多余重复记录)

SQL> select * from nn
  2  ;

        ID       NAME
---------- ----------
         1          2
         1          3
         2          3

SQL>
SQL> rollback;

Rollback complete.

SQL> delete from nn
  2  where (id,name) in (select id,name from nn group by id,name having count(*)
 > 1) and rowid not in(select min(rowid) from nn group by id,name having count(*
) > 1);

2 rows deleted.                --此处为删除多余重复记录(保留一条)

SQL> select * from nn;

        ID       NAME
---------- ----------
         1          1
         1          2
         1          3
         2          3
         3          3

 

3.用DISTINCT方法,有人说对小表比较有用

SQL> select * from nn;

        ID       NAME
---------- ----------
         1          1
         1          1
         1          2
         1          3
         2          3
         3          3
         3          3

7 rows selected.

SQL> create table nn_new as select distinct * from nn;

Table created.

SQL> truncate table nn;

Table truncated.

SQL> insert into nn select * from nn_new;

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from nn;

        ID       NAME
---------- ----------
         2          3
         1          2
         1          3
         1          1
         3          3

SQL> drop table nn_new;

Table dropped.

SQL>

 

好了,就先试这三种方法吧,以后看到新的方法再加进来. 

 

BTW:这两天论坛好像有点奇怪哦,我不能插入代码了,一点那个按钮弹出来的窗口是乱码.只好直接贴上来了.呵呵!