我需要从一个相当大的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
我该怎么做呢?
当前回答
如果重复行的所有列都是相同的,那么下面的查询可以用来删除重复的记录。
SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable
其他回答
alter table MyTable add sno int identity(1,1)
delete from MyTable where sno in
(
select sno from (
select *,
RANK() OVER ( PARTITION BY RowID,Col3 ORDER BY sno DESC )rank
From MyTable
)T
where rank>1
)
alter table MyTable
drop column sno
有时使用软删除机制,其中记录日期以指示删除的日期。在这种情况下,可以使用UPDATE语句根据重复的条目更新该字段。
UPDATE MY_TABLE
SET DELETED = getDate()
WHERE TABLE_ID IN (
SELECT x.TABLE_ID
FROM MY_TABLE x
JOIN (SELECT min(TABLE_ID) id, COL_1, COL_2, COL_3
FROM MY_TABLE d
GROUP BY d.COL_1, d.COL_2, d.COL_3
HAVING count(*) > 1) AS d ON d.COL_1 = x.COL_1
AND d.COL_2 = x.COL_2
AND d.COL_3 = x.COL_3
AND d.TABLE_ID <> x.TABLE_ID
/*WHERE x.COL_4 <> 'D' -- Additional filter*/)
对于包含大约3000万行、重复量有高有低的中等数据表,这种方法非常适用。
DELETE
FROM MyTable
WHERE NOT EXISTS (
SELECT min(RowID)
FROM Mytable
WHERE (SELECT RowID
FROM Mytable
GROUP BY Col1, Col2, Col3
))
);
通过使用下面的查询,我们可以基于单列或多列删除重复的记录。下面的查询是基于两列进行删除。表名为:testing,列名为empno,empname
DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno)
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno)
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)
INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)
--SELECT * FROM car
;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)
DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)