sql删除重复记录

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

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

用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


方法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')