sql server 删除重复数据常用的两种方法（临时表和PARTITION BY）

总结

有两种常用方法可以删除重复数据，首先创建一个示例表

CREATE TABLE test_table (tid int )

INSERT INTO test_table values (1)
INSERT INTO test_table values (1)
INSERT INTO test_table values (1)
INSERT INTO test_table values (1)

INSERT INTO test_table values (2)
INSERT INTO test_table values (2)
INSERT INTO test_table values (2)

方法一

运行以下脚本：

SELECT DISTINCT *
INTO test_duplicate_table
FROM test_table
GROUP BY tid
HAVING COUNT(tid) > 1

DELETE test_table
WHERE tid
IN (SELECT tid
FROM test_duplicate_table)

INSERT test_table
SELECT *
FROM test_duplicate_table

DROP TABLE test_duplicate_table

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

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

这个方法很简单。但是，它要求数据库中有足够的可用空间来临时构建重复的表。由于要移动数据，此方法还会产生开销。

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

方法二

Microsoft SQL Server 2005中引入的ROW_NUMBER函数使此操作更加简单：

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

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

1. 使用ROW_NUMBER函数根据tid对数据进行分区，tid可以是用逗号分隔的一列或多列。
2. 删除接收到大于1的DupRank值的所有记录。此值表示记录是重复的。

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

更多

方法2简单有效，原因如下：

它不需要您将重复的记录临时复制到另一个表中。

它不需要将原始表与其自身连接（例如，通过使用GROUP by和HAVING的组合返回所有重复记录的子查询）。

为了获得最佳性能，您应该在表上有一个相应的索引，该索引使用tid作为索引键，并包括您可能在ORDER BY表达式中使用的任何排序列。

但是，此方法在不支持ROW_NUMBER函数的过时版本的SQL Server中不起作用。在这种情况下，应该使用方法1或类似的方法。