从 SQL 表中删除重复记录

• 下载源代码 - 1.45 KB

引言

一个通用的存储过程，用于从表中检索和删除重复记录，指定用于标识每条记录的自定义字段列表。

背景

当我对客户的生产数据库进行优化时，我发现由于表中存在重复记录，导致创建适当的主键时出现了问题。 在网上搜索了一下，我找到了大量删除重复记录的脚本。 所有现有的脚本都存在两个主要问题：

1. 它们不是通用的，必须针对每个表编写
2. 它们都假设重复记录在所有字段中都是相同的，而不仅仅是主键

在我的情况下，我需要处理 150 个表，而且我不想为每个表都编写一个脚本。 此外，在某些表中，重复记录具有相同的被指定为主键的字段，但其他字段却不同！ 因此，在所有脚本中使用的 Select Distinct 仍然会返回这些记录两次。

因此，我不得不编写自己的脚本来解决这个问题。

使用代码

有两个存储过程

GetDuplicates @TableName, @PK
DeleteDuplicates @TableName, @PK

这两个过程都接受两个字符串参数：表名和用逗号分隔的主键字段列表。 主键字段是唯一标识记录的字段，它们不一定是实际的表主键。

调用示例

--Get all duplicate recordes from table 
--        OrderDetails having unique CustomerID, OrderID and ItemID
EXEC GetDuplicates 'OrderDetails', 'CustomerID, OrderID, ItemID'

这两个存储过程都使用动态 SQL 来实现通用性。 它们还调用函数 GetTableFieldsList 以获取一个字符串，其中包含用逗号分隔的所有表字段。 该函数允许通过添加前缀和后缀参数来设置所有字段的填充函数。 返回列表中的每个字段都将以前缀为前缀，以后缀为后缀。 在参数中使用 @ 允许获取当前字段名称。

调用示例

SELECT dbo.GetTableFieldsList('Customers', 'MAX(', ') AS @')

将返回 MAX(f1) AS f1, MAX(f2) AS f2，... 其中 f1, f2, ... 是 Customers 表的字段。

CREATE     Function GetTableFieldsList(
 @TableName AS VARCHAR(255),
 @FieldPrefix AS VARCHAR(255) = '',
 @FieldSuffix AS VARCHAR(255) = '')
RETURNS VARCHAR(5000)
AS

BEGIN
declare @fields_list as varchar(5000)
set @fields_list = ''
declare names_curr cursor read_only forward_only
for 
select syscolumns.name from syscolumns inner join 
       sysobjects on syscolumns.id = sysobjects.id 
where sysobjects.name=@TableName
order by syscolumns.colid
declare @tmp_field as varchar(255)
declare @tmp_prefix as varchar(255)
declare @tmp_suffix as varchar(255)
open names_curr
fetch next from names_curr into @tmp_field
while (@@FETCH_STATUS = 0)
begin
 set @tmp_prefix = Replace(@FieldPrefix, '@', @tmp_field)
 set @tmp_suffix = Replace(@FieldSuffix, '@', @tmp_field)
 set @tmp_field = @tmp_prefix + @tmp_field + @tmp_suffix
 set @fields_list = CASE WHEN @fields_list='' THEN 
     @tmp_field ELSE @fields_list + ', ' + @tmp_field END
 fetch next from names_curr into @tmp_field
end
close names_curr
deallocate names_curr
RETURN @fields_list
END

第一个存储过程 GetDuplicates 将返回该表的所有重复记录，按主键字段列表 + DuplicateCount 字段排序以指示重复记录的数量。 此过程有两个字符串参数：表名和主键字段列表。 请注意，主键字段列表必须仅包含字段名称，不包含表名。

调用示例

EXEC GetDuplicates 'OrderDetails', 'CustomerID, OrderID, ItemID'

将返回 OrderDetails 表的所有重复记录，这些记录具有相同的 CustomerID、OrderID 和 ItemID 字段。

CREATE    PROCEDURE GetDuplicates(@TableName AS VARCHAR(255), @PK AS VARCHAR(4096))
AS
declare @table_pk as varchar(5000)
declare @tmp_pk as varchar(5000)
set @table_pk = replace(@pk, ' ', '') --Remove blanks
set @tmp_pk = @table_pk
--Add table name prefix
set @table_pk = @TableName+'.'+replace(@table_pk, ',', ', '+@TableName+'.')
--Add table name prefix
set @tmp_pk = '#dup_table_tmp.'+replace(@tmp_pk, ',', ', #dup_table_tmp.')
declare @sql as varchar(5000)
set @sql = 'select ' + @PK
set @sql = @sql + ', count(*) AS DuplicateCount '
set @sql = @sql + 'INTO #dup_table_tmp from ' + @TableName
set @sql = @sql + ' group by ' + @PK
set @sql = @sql + ' having count(*) > 1; '
set @sql = @sql + 'select '
set @sql = @sql + dbo.GetTableFieldsList(@TableName, @TableName+'.', ' as @')
set @sql = @sql + ', #dup_table_tmp.DuplicateCount'
set @sql = @sql + ' from ' + @TableName + ' inner join #dup_table_tmp'
set @sql = @sql + ' on Checksum(' + @table_pk + ') = Checksum(' + @tmp_pk + ')'
set @sql = @sql + ' order by ' + @table_pk
--print @sql
exec (@sql)

下一个存储过程 DeleteDuplicates 将删除该表的所有重复记录，为每个主键字段组合仅保留一条记录。 假设重复记录可能具有与主键不同的字段，我们将使用 MAX 聚合函数来选择在所有字段中具有最大值的记录。

调用示例

EXEC DeleteDuplicates 'OrderDetails', 'CustomerID, OrderID, ItemID'

将删除 OrderDetails 表的所有重复记录，这些记录具有相同的 CustomerID、OrderID 和 ItemID 字段，仅保留一条具有相同 CustomerID、OrderID 和 ItemID 的记录。

CREATE PROCEDURE DeleteDuplicates(@TableName AS VARCHAR(255), @PK AS VARCHAR(4096))
AS
declare @sql as varchar(5000)
set @sql = 'begin transaction; '
set @sql = @sql + 'if exists (select ' + @PK + ' from ' + @TableName
set @sql = @sql + ' group by ' + @PK
set @sql = @sql + ' having count(*) > 1)'
set @sql = @sql + ' begin '
set @sql = @sql + 'select '
set @sql = @sql + dbo.GetTableFieldsList(@TableName, 'max(', ') as @')
set @sql = @sql + ' INTO #dup_table_tmp from ' + @TableName
set @sql = @sql + ' group by ' + @PK + '; '
set @sql = @sql + 'delete from ' + @TableName + '; '
set @sql = @sql + 'insert into ' + @TableName
set @sql = @sql + ' select * from #dup_table_tmp;'
set @sql = @sql + ' end '
set @sql = @sql + 'commit;'
--print @sql
exec (@sql)

在工作期间，此过程会将表的过滤内容复制到一个临时表，然后将所有内容选择回原始表。

附带的 zip 文件包括存储过程和函数创建脚本。 还有一个示例脚本用于创建表、填充一些测试数据并执行这些过程。

历史

• 2007 年 5 月 13 日 - 第一个版本。