在SQL中删除反复记录(多种办法)[MSSQL防范]

赞助商链接

分享到： QQ空间 新浪微博 腾讯微博 人人网

　　本文“在SQL中删除反复记录(多种办法)[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
?? 学习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

?

?

2. 删除的几种办法:

?

（1）通过成立暂时表来实现

SQL>create table temp_emp as (select distinct * from employee)?

SQL> truncate table employee; (清空employee表的数据）

SQL> insert into employee select * from temp_emp; ?(再将暂时表里的内容插回来）

?

( 2）通过唯一rowid实现删除反复记录.在Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid肯定了每条记录是在Oracle中的哪一个数据文件、块、行上.在反复的记录中,大概全部列的内容都相同,但rowid不会相同,所以只要肯定出反复记录中那些具有最大或最小rowid的便可以了,别的全部删除.

SQL>delete from employee e2 where rowid not in (
??????? select max(e1.rowid) from employee e1 where

??????? e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);--这里用min(rowid)也可以.

?

SQL>delete from employee e2 where rowid <(
??????? select max(e1.rowid) from employee e1 where
??????? e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and

????????????????? e1.salary=e2.salary);

?

（3）也是通过rowid,但效率更高.

SQL>delete from employee where rowid not in (
??????? select max(t1.rowid) from employee t1 group by

???????? t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以.

?

??? EMP_ID EMP_NAME???????????????????????????????????? SALARY

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

???????? 1 sunshine????????????????????????????????????? 10000

???????? 3 xyz???? ??????????????????????????????????????? 30000

???????? 2 semon???????????????????????????????????????? 20000

?

?

?

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