通过

使用脚本从 SQL Server 表中删除重复行

  • 项目

本文提供了一个脚本，可使用此脚本从 Microsoft SQL Server 表中删除重复行。

原始产品版本：SQL Server
原始 KB 编号： 70956

摘要

可使用两种常用方法从 SQL Server 表中删除重复记录。 要进行演示，请首先创建示例表和数据：

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

然后，尝试以下方法从表中删除重复行。

方法 1

运行以下脚本：

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

此脚本按给定顺序执行以下操作：

  • 将原始表中任何重复行的一个实例移动到重复表。
  • 从原始表中删除所有同样位于重复表中的行。
  • 将重复表中的行移回原始表。
  • 删除重复表。

此方法很简单。 但是，它要求数据库中有足够的可用空间来临时生成重复表。 此方法也会因为移动数据而产生开销。

此外，如果表有 IDENTITY 列，则在将数据还原到原始表时，必须使用 SET IDENTITY_INSERT ON。

方法 2

Microsoft SQL Server 2005 中引入的 ROW_NUMBER 函数使此操作要简单得多：

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1

此脚本按给定顺序执行以下操作：

  • 使用 ROW_NUMBER 函数根据 key_value（可能是以逗号分隔的一列或多列）对数据进行分区。
  • 删除所有收到大于 1 的 DupRank 值的记录。 此值指定记录是重复项。

(SELECT NULL)由于 表达式，脚本不会根据任何条件对分区数据进行排序。 如果删除重复项的逻辑需要根据其他列的排序顺序选择要删除哪些记录以及要保留哪些记录，则可以使用 ORDER BY 表达式执行此操作。

更多信息

由于以下原因，方法 2 简单有效：

  • 它不需要将重复记录临时复制到另一个表。
  • 它不需要将原始表与自身联接 (例如，使用子查询，该子查询使用 和 HAVING) 的组合GROUP BY返回所有重复记录。
  • 为了获得最佳性能，应在表上具有相应的索引，该索引使用 key_value 作为索引键，并包括可能在表达式中使用的 ORDER BY 任何排序列。

但是，此方法不适用于不支持 ROW_NUMBER 函数的过时版本的 SQL Server。 在这种情况下，应改用 方法 1 或一些类似的方法。