我需要从一个相当大的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 A
FROM TABLE A,
TABLE B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.UNIQUEFIELD > B.UNIQUEFIELD
其他回答
DELETE
FROM
table_name T1
WHERE
rowid > (
SELECT
min(rowid)
FROM
table_name T2
WHERE
T1.column_name = T2.column_name
);
我想提一下这种方法,它可能是有帮助的,并且适用于所有SQL服务器: 通常只有一到两个副本,副本的id和数量是已知的。在这种情况下:
SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
另一种可能的方法是
;
--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策略则更加灵活。
可能有利于哈希聚合方法的因素是
分区列上没有有用的索引 相对较少的组,每组的重复数相对较多
在第二种情况的极端版本中(如果每个组中有很多重复的组),还可以考虑简单地插入要保留到新表中的行,然后截断原始的行并将它们复制回来,以最大限度地减少日志记录,而不是删除非常高比例的行。
这是另一篇关于删除重复的好文章。
它讨论了为什么它很难:“SQL是基于关系代数的,在关系代数中不能出现重复,因为在一个集合中不允许重复。”
临时表解决方案,以及两个mysql示例。
将来,您是打算在数据库级别还是从应用程序的角度防止它?我建议数据库级别,因为你的数据库应该负责维护引用完整性,开发人员只会造成问题;)
获取重复的行:
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING COUNT(*) > 1
删除重复的行。
DELETE users
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM users
GROUP BY name, email);