这段时间因项目需要导入原有的数据到新库中,遭遇了数据重复的问题,需删除重复的数据。我采用游标的方法解决此问题。 Sql 语句如下以免忘记。
删除重复数据,只保留记录号最大的
![](http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->
declare
mycursor
cursor
for
select addinip from testdb group by addinip having count (addinip) > 1
open mycursor
declare @ip sysname
fetch next from mycursor into @ip
while ( @@fetch_status = 0 )
begin
print @ip
declare @aid int
select @aid = max (autoid) from testdb where addinip = @ip
print @aid
-- 删除重复数据,只保留记录号最大的那条记录
delete from testdb where autoid <> @aid and addinip = @ip
fetch next from mycursor into @ip
end
close mycursor
deallocate mycursor
for
select addinip from testdb group by addinip having count (addinip) > 1
open mycursor
declare @ip sysname
fetch next from mycursor into @ip
while ( @@fetch_status = 0 )
begin
print @ip
declare @aid int
select @aid = max (autoid) from testdb where addinip = @ip
print @aid
-- 删除重复数据,只保留记录号最大的那条记录
delete from testdb where autoid <> @aid and addinip = @ip
fetch next from mycursor into @ip
end
close mycursor
deallocate mycursor
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-626230/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-626230/