我需要从一个相当大的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 t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid
邮政:
delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid
其他回答
我想提一下这种方法,它可能是有帮助的,并且适用于所有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 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)
在postgresql中删除重复行的一个非常简单的方法。
DELETE FROM table1 a
USING table1 b
WHERE a.id < b.id
AND a.column1 = b.column1
AND a.column2 = b.column2;
我想我应该分享我的解决方案,因为它在特殊情况下是有效的。 在我的例子中,有重复值的表没有外键(因为这些值是从另一个db复制的)。
begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2
-- insert distinct values into temp
insert into #temp
select distinct *
from tableName
-- delete from source
delete from tableName
-- insert into source from temp
insert into tableName
select *
from #temp
rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!
PS:在处理这样的事情时,我总是使用事务,这不仅确保了所有事情都作为一个整体执行,而且还允许我在没有任何风险的情况下进行测试。但是当然你应该做个备份,以防万一……