Oracle 下删除重复数据测试

Oracle 测试删除重复数据


SQL>create table test 
   (na1 int,na2 int,na3 int,na4 int,na5 int,na6 int,na7 int,na8 int,na9 int);

SQL>insert into test values ('1','1','1','1','1','1','1','1','1');
SQL>insert into test values ('1','1','1','1','1','1','1','1','2');
SQL>insert into test values ('1','1','1','1','1','1','1','1','3');
SQL>insert into test values ('1','1','1','1','1','1','1','1','4');



SQL> select * from test;

       NA1        NA2        NA3        NA4        NA5        NA6        NA7        NA8        NA9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          2
         1          1          1          1          1          1          1          1          3
         1          1          1          1          1          1          1          1          3
         1          1          1          1          1          1          1          1          4
         1          1          1          1          1          1          1          1          4


删除na9单列重复的字段
delete from  test
where  na9  in (select  na9  from test   group  by  na9   having  count(na9) > 1)
and rowid not in (select min(rowid) from  test  group by na9 having count(na9)>1);


SQL> select * from test;

       NA1        NA2        NA3        NA4        NA5        NA6        NA7        NA8        NA9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          2
         1          1          1          1          1          1          1          1          3
         1          1          1          1          1          1          1          1          4



再插入几行重复的数据

SQL>insert into test values ('1','1','1','1','1','1','1','2','4');
SQL>insert into test values ('1','1','1','1','1','1','1','3','4');


SQL> select * from  test;

       NA1        NA2        NA3        NA4        NA5        NA6        NA7        NA8        NA9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1          1          2          4
         1          1          1          1          1          1          1          3          4
         1          1          1          1          1          1          1          2          4
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          2
         1          1          1          1          1          1          1          1          3
         1          1          1          1          1          1          1          1          3
         1          1          1          1          1          1          1          1          4
         1          1          1          1          1          1          1          1          4
         1          1          1          1          1          1          1          3          4

13 rows selected.


删除na9列的重复数据

SQL>delete from  test
    where  na9  in 
   (select  na9  from test   group  by  na9   having  count(na9) > 1)
    and rowid not in 
   (select min(rowid) from  test  group by na9 having count(na9)>1);


SQL> SELECT * FROM TEST;

       NA1        NA2        NA3        NA4        NA5        NA6        NA7        NA8        NA9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1          1          2          4
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          2
         1          1          1          1          1          1          1          1          3



生产情况往往是删除所有字段都重复的数据

SQL> delete from  test  a
  2  where (a.na1,a.na2,a.na4,a.na3,a.na5,a.na6,a.na7,a.na8,a.na9) in  
  3  (select na1,na2,na3,na4,na5,na6,na7,na8,na9 from cz  
  4  group by na1,na2,na3,na4,na5,na6,na7,na8,na9  having count(*) > 1)
  5  and rowid not in 
(select min(rowid) from test group by na1,na2,na3,na4,na5,na6,na7,na8,na9  having count(*)>1);  6  

8 rows deleted.

SQL> select * from test;

       NA1        NA2        NA3        NA4        NA5        NA6        NA7        NA8        NA9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          1          1          1          2          4
         1          1          1          1          1          1          1          3          4
         1          1          1          1          1          1          1          1          1
         1          1          1          1          1          1          1          1          3
         1          1          1          1          1          1          1          1          4