方案1:
1、將重復的記錄記入temp1表:
select [標志字段id],count(*) into temp1 from [表名]group by [標志字段id]having count(*)>12、將不重復的記錄記入temp1表:
insert temp1select [標志字段id],count(*) from [表名]group by [標志字段id]having count(*)=13、作一個包含所有不重復記錄的表：
select * into temp2 from [表名]where 標志字段id in(select 標志字段id from temp1)4、刪除重復表:delete [表名]
5、恢復表：
insert [表名]select * from temp26、刪除臨時表:
drop table temp1drop table temp2方案2:
declare @max integer,@id integerdeclare cur_rows cursor local for select id,count(*) from 表名 group by id having count(*) > 1open cur_rowsfetch cur_rows into @id,@maxwhile @@fetch_status=0beginselect @max = @max -1set rowcount @maxdelete from 表名 where id = @idfetch cur_rows into @id,@maxendclose cur_rowsset 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_distcreate procedure up_distinct(@t_name varchar(30),@f_key varchar(30))--f_key表示是分組字段﹐即主鍵字段asbegindeclare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integerselect @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_keyif @type=56select @sql = ''delete from ''+@t_name+'' where '' + @f_key+'' = ''+ @id if @type=167select @sql = ''delete from ''+@t_name+'' where '' + @f_key+'' = ''+''''''''+ @id +'''''''' exec(@sql)fetch cur_rows into @id,@max end close cur_rows deallocate cur_rowsset rowcount 0endselect * from systypesselect
