sql语句如何删除重复记录?四种方法

发布时间:2019-10-21编辑:脚本学堂
本文介绍了sql语句删除重复记录的四种方法,可以通过唯一rowid实现删除重复记录,还可以建临时表来实现,需要的朋友参考下。

本节内容:
sql语句删除重复记录的四种方法

问题:如何把具有相同字段的记录删除,只留下一条。
 
例如:表test里有id,name字段,如果有name相同的记录只留下一条,其余的删除。name的内容不定,相同的记录数不定。
 
sql语句删除sql数据库中重复记录的四种方法:

方法1:
 
1、将重复的记录记入temp1表
 

select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1

2、将不重复的记录记入temp1表
 

insert temp1
select [标志字段id],count(*) from [表名]
group by [标志字段id]
having count(*)=1
 

3、作一个包含所有不重复记录的表
 

select * into temp2 from [表名]
where 标志字段id in(select 标志字段id from temp1)

4、删除重复表:delete [表名]

5、恢复表
 

insert [表名]
select * from temp2

6、删除临时表
 

drop table temp1
drop table temp2

更多sql语句的用法,请移步至sql语句大全,查阅更详细的介绍。
 
方法2:
 

declare @max integer,@id integer
declare cur_rows cursor local for
select id,count(*) from 表名 group by id having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where id = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

注:set rowcount @max - 1表示当前缓冲区只容纳@max-1条记录,如果有十条重复的,就刪除10条,一定会留一条的。也可以写成delete from 表名。
 
方法3:
 

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
exec up_distinct 'a_dist','id'
select * from a_dist
create procedure up_distinct(@t_name varchar(30)
,@f_key varchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare @max integer,@id varchar(30) ,
@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor
for select '+@f_key+' ,count(*) from '
+@t_name +' group by ' +@f_key +' having count(*) > 1'
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns
where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+'
where ' + @f_key+' = '+ @id
if @type=167
select @sql = 'delete from '+@t_name+'
where ' + @f_key+' = '+''''+ @id +''''
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from systypes
select * from syscolumns where
id = object_id('a_dist')
 

方法4:
可以用IGNORE_DUP_KEY:
 

create table dup (id int identity not null,
name varchar(50)not null)
go
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('cdefg')
insert into dup(name) values ('xyz')
insert into dup(name) values ('xyz')
go
select *
from dup
go
create table tempdb..wk(id int not null,
name varchar(50)not null)
go
create unique index idx_remove_dup
on tempdb..wk(name)
with IGNORE_DUP_KEY
go
INSERT INTO tempdb..wk (id, name)
select id, name
from dup
go
select *
from tempdb..wk
go
delete from dup
go
set identity_insert dup on
INSERT INTO dup (id, name)
select id, name
from tempdb..wk
go
set identity_insert dup off
go
select *
from dup
go
 

注释:此处delete原表,再加入不重复的值。大家也可以通过join只delete原表中重复的值。

在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
 

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