四种方法教你如何用SQL语句删除重复记录

<script src="http://www.itcncom.com/itcncom_ad/300250.js" type="text/javascript"></script><script type="text/javascript"><!-- google_ad_client = "pub-6813481064858465"; google_ad_width = 300; google_ad_height = 250; google_ad_format = "300x250_as"; google_ad_type = "text"; //2007-07-22: 300X250 google_ad_channel = "1070375789"; google_color_border = "40aae0"; google_color_bg = "FFFFFF"; google_color_link = "0000EE"; google_color_text = "000000"; google_color_url = "000000"; google_ui_features = "rc:0"; //--> </script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script><iframe name="google_ads_frame" marginwidth="0" marginheight="0" src="http://pagead2.googlesyndication.com/pagead/ads?client=ca-pub-6813481064858465&amp;dt=1194593872796&amp;lmt=1192807592&amp;format=300x250_as&amp;output=html&amp;correlator=1194593872796&amp;channel=1070375789&amp;url=http%3A%2F%2Fwww.itcncom.com%2Fsql%2F20070506_904.html&amp;color_bg=FFFFFF&amp;color_text=000000&amp;color_link=0000EE&amp;color_url=000000&amp;color_border=40aae0&amp;ad_type=text&amp;ref=http%3A%2F%2Fwww.itcncom.com%2Fsql%2F20070601_1470.html&amp;ui=rc%3A0&amp;cc=100&amp;ga_vid=1108614851.1194593873&amp;ga_sid=1194593873&amp;ga_hid=145667589&amp;flash=9&amp;u_h=1024&amp;u_w=1280&amp;u_ah=994&amp;u_aw=1280&amp;u_cd=32&amp;u_tz=480&amp;u_his=5&amp;u_java=true" frameborder="0" width="300" scrolling="no" height="250" allowtransparency="allowtransparency"></iframe>

<!--www.itcncom.com广告代码结束-->例如：表test里有id,name字段,如果有name相同的记录只留下一条，其余的删除。name的内容不定，相同的记录数不定。

方案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’)