我需要从一个相当大的SQL Server表(即300,000+行)中删除重复的行。
当然,由于RowID标识字段的存在,这些行不会完全重复。
MyTable
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
我该怎么做呢?
我需要从一个相当大的SQL Server表(即300,000+行)中删除重复的行。
当然,由于RowID标识字段的存在,这些行不会完全重复。
MyTable
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
我该怎么做呢?
当前回答
DELETE
FROM
table_name T1
WHERE
rowid > (
SELECT
min(rowid)
FROM
table_name T2
WHERE
T1.column_name = T2.column_name
);
其他回答
另一种可能的方法是
;
--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY ( SELECT 0)) RN
FROM #MyTable)
DELETE FROM cte
WHERE RN > 1;
我在上面使用ORDER BY (SELECT 0),因为在出现平局的情况下,保留哪一行是任意的。
例如,要以RowID顺序保存最新的一个,您可以使用order BY RowID DESC
执行计划
它的执行计划通常比接受的答案更简单和更有效,因为它不需要自连接。
然而,情况并非总是如此。GROUP BY解决方案可能会优先于选择散列聚合而不是流聚合的情况。
ROW_NUMBER解决方案总是给出几乎相同的计划,而GROUP BY策略则更加灵活。
可能有利于哈希聚合方法的因素是
分区列上没有有用的索引 相对较少的组,每组的重复数相对较多
在第二种情况的极端版本中(如果每个组中有很多重复的组),还可以考虑简单地插入要保留到新表中的行,然后截断原始的行并将它们复制回来,以最大限度地减少日志记录,而不是删除非常高比例的行。
假设没有空值,您GROUP BY唯一的列,并选择最小(或最大)RowId作为要保留的行。然后,删除所有没有行id的内容:
DELETE FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) as RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) as KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL
如果您使用的是GUID而不是整数,则可以进行替换
MIN(RowId)
with
CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))
另一个简单的解决方案可以在这里粘贴的链接中找到。这个方法很容易掌握,似乎对大多数类似的问题都很有效。虽然它是为SQL Server,但所使用的概念是可以接受的。
以下是链接页面的相关部分:
考虑以下数据:
EMPLOYEE_ID ATTENDANCE_DATE
A001 2011-01-01
A001 2011-01-01
A002 2011-01-01
A002 2011-01-01
A002 2011-01-01
A003 2011-01-01
那么我们如何删除这些重复的数据呢?
首先,使用以下代码在表中插入一个标识列:
ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)
使用下面的代码来解决它:
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)
快速和脏删除精确重复的行(小表):
select distinct * into t2 from t1;
delete from t1;
insert into t1 select * from t2;
drop table t2;
下面的查询用于删除重复的行。本例中的表以ID作为标识列,具有重复数据的列是Column1、Column2和Column3。
DELETE FROM TableName
WHERE ID NOT IN (SELECT MAX(ID)
FROM TableName
GROUP BY Column1,
Column2,
Column3
/*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
nullable. Because of semantics of NOT IN (NULL) including the clause
below can simplify the plan*/
HAVING MAX(ID) IS NOT NULL)
下面的脚本显示GROUP BY、HAVING、ORDER BY在一个查询中的用法,并返回带有重复列及其计数的结果。
SELECT YourColumnName,
COUNT(*) TotalCount
FROM YourTableName
GROUP BY YourColumnName
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC