我需要从一个相当大的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
我该怎么做呢?
当前回答
现在让我们看看elasticalsearch表,这个表有重复的行,Id是相同的uniq字段。我们知道如果某个id存在于某个组条件下,那么我们可以删除该组作用域之外的其他行。我的举止表明了这一标准。
很多情况下,这个线程是在类似的状态,我。只需根据删除重复(重复)行的情况更改目标组条件。
DELETE
FROM elasticalsearch
WHERE Id NOT IN
(SELECT min(Id)
FROM elasticalsearch
GROUP BY FirmId,FilterSearchString
)
干杯
其他回答
哦,当然。使用临时表。如果你想要一个“工作”的单一的、性能不太好的语句,你可以使用:
DELETE FROM MyTable WHERE NOT RowID IN
(SELECT
(SELECT TOP 1 RowID FROM MyTable mt2
WHERE mt2.Col1 = mt.Col1
AND mt2.Col2 = mt.Col2
AND mt2.Col3 = mt.Col3)
FROM MyTable mt)
基本上,对于表中的每一行,子选择将查找与所考虑行的完全相同的所有行的顶部RowID。因此,您最终会得到一个表示“原始”非重复行的RowIDs列表。
我知道这个问题已经回答了,但我已经创建了非常有用的sp,它将为表副本创建一个动态删除语句:
CREATE PROCEDURE sp_DeleteDuplicate @tableName varchar(100), @DebugMode int =1
AS
BEGIN
SET NOCOUNT ON;
IF(OBJECT_ID('tempdb..#tableMatrix') is not null) DROP TABLE #tableMatrix;
SELECT ROW_NUMBER() OVER(ORDER BY name) as rn,name into #tableMatrix FROM sys.columns where [object_id] = object_id(@tableName) ORDER BY name
DECLARE @MaxRow int = (SELECT MAX(rn) from #tableMatrix)
IF(@MaxRow is null)
RAISERROR ('I wasn''t able to find any columns for this table!',16,1)
ELSE
BEGIN
DECLARE @i int =1
DECLARE @Columns Varchar(max) ='';
WHILE (@i <= @MaxRow)
BEGIN
SET @Columns=@Columns+(SELECT '['+name+'],' from #tableMatrix where rn = @i)
SET @i = @i+1;
END
---DELETE LAST comma
SET @Columns = LEFT(@Columns,LEN(@Columns)-1)
DECLARE @Sql nvarchar(max) = '
WITH cteRowsToDelte
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY '+@Columns+' ORDER BY ( SELECT 0)) as rowNumber,* FROM '+@tableName
+')
DELETE FROM cteRowsToDelte
WHERE rowNumber > 1;
'
SET NOCOUNT OFF;
IF(@DebugMode = 1)
SELECT @Sql
ELSE
EXEC sp_executesql @Sql
END
END
如果你创建这样的表格
IF(OBJECT_ID('MyLitleTable') is not null)
DROP TABLE MyLitleTable
CREATE TABLE MyLitleTable
(
A Varchar(10),
B money,
C int
)
---------------------------------------------------------
INSERT INTO MyLitleTable VALUES
('ABC',100,1),
('ABC',100,1), -- only this row should be deleted
('ABC',101,1),
('ABC',100,2),
('ABCD',100,1)
-----------------------------------------------------------
exec sp_DeleteDuplicate 'MyLitleTable',0
它将从表中删除所有重复项。如果运行它时不带第二个参数,它将返回一条SQL语句来运行。
如果您需要排除任何列,只需在调试模式下运行它,获取代码并按照您的喜好修改它。
从应用程序级别(不幸的是)。我同意防止重复的正确方法是在数据库级别上使用唯一的索引,但是在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)吗?
删除重复记录
在这种情况下,大于操作符删除除第一条记录以外的所有记录
u1 FROM users u1 JOIN users u2 u1的地方。Id > u2.id 和u1.email = u2.email
<小于操作符在这种情况下删除除最后一条记录以外的所有记录
u1 FROM users u1 JOIN users u2 u1的地方。Id < u2.id 和u1.email = u2.email
DELETE
FROM MyTable
WHERE NOT EXISTS (
SELECT min(RowID)
FROM Mytable
WHERE (SELECT RowID
FROM Mytable
GROUP BY Col1, Col2, Col3
))
);