在SQL SERVER 2000中,由于没有了递归查询,要删除某表的重复数据,相对比较麻烦,比如作分组聚合,然后取重复次数最多的主键进行删除。脚本如下:
/**
快速删除重复数据(效率较高,但是不能按照XX字段排序,执行的时间复杂度为重复数最大的字段)
@TableName:表名称
@PrimaryKey:主键名称
@DoubleValue:重复的字段
*/
create procedure DeleteDoubleDataFast
@TableName varchar(100),
@PrimaryKey varchar(100),
@DoubleValue varchar(100)
as
declare @sql varchar(5000)
set @sql =
' declare @mountid int'+
' select @mountid = count(*) from (select COUNT(*) as amount from '+@TableName+' group by '+@DoubleValue+' having COUNT(*) > 1) a'+
' while @mountid <>0'+
' begin'+
' delete from '+@TableName+' where '+@PrimaryKey+' in(select max('+@PrimaryKey+') from '+@TableName+' group by '+@DoubleValue+' having COUNT(*) > 1) '+
' select @mountid = count(*) from (select COUNT(*) as amount from '+@TableName+' group by '+@DoubleValue+' having COUNT(*) > 1) a'+
' print @mountid'+
' end'
print @sql
exec(@sql)
该脚本的缺点就是不能按照XX排序。
在SQL SERVER2005以后,由于有了递归查询,则可以分组聚合,再给聚合的结果加上行号,然后取出行号为1的数据,其他的删除则可,效率也高,100万的数据几秒就搞定。脚本如下:
/**
最快速速度删除重复数据
@DoubleField:重复的字段
@Tablename:表名称
@OrderField:排序字段
@PrimaryKeyName:主键
*/
create procedure DeleteDoubleDataVeryFast
@DoubleField varchar(100),
@Tablename varchar (100),
@OrderField varchar(100),
@PrimaryKeyName varchar(100)
as
declare @sql varchar(5000)
set @sql=
' with numberedRows as'+
' ('+
' select ROW_NUMBER() over(PARTITION by '+@DoubleField+' order by '+@OrderField+') as RowNumber,'+@PrimaryKeyName+' from '+@Tablename +
' )'+
' delete from '+@Tablename+' where '+@PrimaryKeyName+' in(select '+@PrimaryKeyName+' from numberedRows where RowNumber<>1)'
exec(@sql)
go