sql去除重复记录

习sql有一段时间了，发现在我建了一个用来测试的表（没有建索引）中出 现了许多的重复记录。后来总结了一些删除重复记录的方法，在Oracle中，可以通过唯一rowid实现删除重复记录；还可以建临时表来实现...这个只 提到其中的几种简单实用的方法，希望可以和大家分享（以表employee为例）。

 

 

SQL> desc employee

 

 

Name                                      Null?    Type
----------------------------------------- -------- ------------------

 

emp_id                                                NUMBER(10)
emp_name                                           VARCHAR2(20)

 

salary                                                NUMBER(10,2)

 

 

可以通过下面的语句查询重复的记录：

 

 

SQL> select * from employee ;

 

    EMP_ID EMP_NAME                                   SALARY

 

---------- ---------------------------------------- ----------

 

         1 sunshine                                      10000

 

         1 sunshine                                      10000

 

         2 semon                                          20000

 

         2 semon                                         20000

 

         3 xyz                                           30000

 

         2 semon                                         20000

 

SQL> select distinct * from employee ;

 

 

    EMP_ID EMP_NAME                                     SALARY

 

---------- ---------------------------------------- ----------

 

         1 sunshine                                      10000

 

         2 semon                                         20000

 

          3 xyz                                             30000

 

SQL> select * from employee group by emp_id , emp_name , salary having count (*)> 1

 

 

    EMP_ID EMP_NAME                                     SALARY

 

---------- ---------------------------------------- ----------

 

         1 sunshine                                      10000

 

         2 semon                                          20000

 

SQL> select * from employee e1

 

where rowid in ( select max ( rowid ) from employe e2
where e1.emp_id = e2.emp_id and

 

e1.emp_name = e2.emp_name and e1.salary = e2.salary );

 

 

 

    EMP_ID EMP_NAME                                     SALARY

 

---------- ---------------------------------------- ----------

 

         1 sunshine                                      10000

 

         3 xyz                                             30000

 

         2 semon                                         20000