四种SQL语句删除重复记录的方法

最新推荐文章于 2023-05-02 11:08:04 发布

lanman

最新推荐文章于 2023-05-02 11:08:04 发布

阅读量1.1k

点赞数

分类专栏： SQL SERVER 文章标签： sql insert delete integer table null

SQL SERVER 专栏收录该内容

32 篇文章 0 订阅

订阅专栏

问题：如何把具有相同字段的记录删除，只留下一条。

例如：表test里有id，name字段，如果有name相同的记录只留下一条，其余的删除。name的内容不定，相同的记录数不定。

用SQL语句删除重复记录的四种方法：

方法1：

1、将重复的记录记入temp1表

select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1 软件开发网 www.mscto.com

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 软件开发网 www.mscto.com

方法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原表中重复的值。