【原创】用SQL语句删除重复记录的方法总结
例如:表test里有id,name字段,如果有name相同的记录只留下一条,其余的删除。name的内容不定,相同的记录数不定。
本人原创方法:
delete from users a
left join (select id = max(id), usercd from users where usercd in (select usercd from users group by usercd having count(usercd)>1) group by usercd) b
on a.usercd = b.usercd
where a.id <> b.id
and isnull(b.usercd, '') <> ''
以下来自网络:
select [标志字段id],count(*) into temp1 from [表名] |
2、将不重复的记录记入temp1表
insert temp1 |
select * into temp2 from [表名] |
insert [表名] |
6、删除临时表
drop table temp1 |
declare @max integer,@id integer |
注:set rowcount @max - 1表示当前缓冲区只容纳@max-1条记录,如果有十条重复的,就刪除10条,一定会留一条的。也可以写成delete from 表名。
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') |
可以用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 |
select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp |