我需要从一个相当大的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
我该怎么做呢?
当前回答
对于表结构
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 MyTable t1
INNER JOIN MyTable t2
WHERE t1.RowID > t2.RowID
AND t1.Col1 = t2.Col1
AND t1.Col2=t2.Col2
AND t1.Col3=t2.Col3;
我假设RowID是一种自动递增,其余列有重复的值。
其他回答
DELETE
FROM
table_name T1
WHERE
rowid > (
SELECT
min(rowid)
FROM
table_name T2
WHERE
T1.column_name = T2.column_name
);
从应用程序级别(不幸的是)。我同意防止重复的正确方法是在数据库级别上使用唯一的索引,但是在SQL Server 2005中,一个索引只能是900字节,而我的varchar(2048)字段打破了这一规定。
我不知道它的性能如何,但我认为您可以编写一个触发器来强制执行这一点,即使您不能直接使用索引。喜欢的东西:
-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism
ON stories
after INSERT, UPDATE
AS
DECLARE @cnt AS INT
SELECT @cnt = Count(*)
FROM stories
INNER JOIN inserted
ON ( stories.story = inserted.story
AND stories.story_id != inserted.story_id )
IF @cnt > 0
BEGIN
RAISERROR('plagiarism detected',16,1)
ROLLBACK TRANSACTION
END
另外,varchar(2048)对我来说听起来很可疑(生活中有些东西是2048字节,但这很少见);它真的应该不是varchar(max)吗?
我想提一下这种方法,它可能是有帮助的,并且适用于所有SQL服务器: 通常只有一到两个副本,副本的id和数量是已知的。在这种情况下:
SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
在postgresql中删除重复行的一个非常简单的方法。
DELETE FROM table1 a
USING table1 b
WHERE a.id < b.id
AND a.column1 = b.column1
AND a.column2 = b.column2;
DELETE LU
FROM (SELECT *,
Row_number()
OVER (
partition BY col1, col1, col3
ORDER BY rowid DESC) [Row]
FROM mytable) LU
WHERE [row] > 1