【网学网提醒】：网学会员为您提供用SQL语句删除重复记录的四种方法_v0参考，解决您在用SQL语句删除重复记录的四种方法_v0学习中工作中的难题，参考学习。
用SQL语句删除重复记录的四种方法(1)
问题:如何把具有相同字段的记录删除,只留下一条.例如:表test里有id,name字段,如果有name相同的记录只留下一条,其余的删除.name的内容不定,相同的记录数不定.用SQL语句删除重复记录的四种方法:方法1::1,将重复的记录记入temp1表
select[标志字段id],count(*)intotemp1from[表名]groupby[标志字段id]havingcount(*)>1
2,将不重复的记录记入temp1表
inserttemp1select[标志字段id],count(*)from[表名]groupby[标志字段id]havingcount(*)=1
3,作一个包含所有不重复记录的表
select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1)
4,删除重复表:delete[表名]5,恢复表
insert[表名]select*fromtemp2
6,删除临时表
droptabletemp1droptabletemp2
方法2::
declare@maxinteger,@idintegerdeclarecur_rowscursorlocalforselectid,count(*)from表名groupbyidhavingcount(*)>1opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0beginselect@max=@max-1setrowcount@maxdeletefrom表名whereid=@idfetchcur_rowsinto@id,@maxendclosecur_rowssetrowcount0
注:setrowcount@max-1表示当前缓冲区只容纳@max-1条记录,如果有十条重复的,就删除10条,一定会留一条的.也可以写成deletefrom表名.方法3::
createtablea_dist(idint,namevarchar(20))
insertintoa_distvalues(1,'abc')insertintoa_distvalues(1,'abc')insertintoa_distvalues(1,'abc')insertintoa_distvalues(1,'abc')execup_distinct'a_dist','id'select*froma_distcreateprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))--f_key表示是分组字段，即主键字段asbegindeclare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeintegerselect@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'exec(@sql)opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0beginselect@max=@max-1setrowcount@maxselect@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_keyif@type=56select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@idif@type=167select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''exec(@sql)fetchcur_rowsinto@id,@maxendclosecur_rowsdeallocatecur_rowssetrowcount0endselect*fromsystypesselect*fromsyscolumnswhereid=object_id('a_dist')
方法4::可以用IGNORE_DUP_KEY:
createtabledup(idintidentitynotnull,namevarchar(50)notnull)goinsertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('abc')
insertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('cdefg')insertintodup(name)values('xyz'
)insertintodup(name)values('xyz')goselect*fromdupgocreatetabletempdb..wk(idintnotnull,namevarchar(50)notnull)gocreateuniqueindexidx_remove_dupontempdb..wk(name)withIGNORE_DUP_KEYgoINSERTINTOtempdb..wk(id,name)selectid,namefromdupgoselect*fromtempdb..wkgodeletefromdupgosetidentity_insertdupon
INSERTINTOdup(id,name)selectid,namefromtempdb..wkgosetidentity_insertdupoffgoselect*fromdupgo
注释:此处delete原表,再加入不重复的值.大家也可以通过join只delete原表中重复的值.
