我需要从一个相当大的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
FROM
TABLE_NAME
WHERE FIRST_COLUMNS
IN(
SELECT * FROM
( SELECT MIN(FIRST_COLUMNS)
FROM TABLE_NAME
GROUP BY
FIRST_COLUMNS,
SECOND_COLUMNS
HAVING COUNT(FIRST_COLUMNS) > 1
) temp
)
注意:在运行查询之前最好模拟查询。
其他回答
这是另一篇关于删除重复的好文章。
它讨论了为什么它很难:“SQL是基于关系代数的,在关系代数中不能出现重复,因为在一个集合中不允许重复。”
临时表解决方案,以及两个mysql示例。
将来,您是打算在数据库级别还是从应用程序的角度防止它?我建议数据库级别,因为你的数据库应该负责维护引用完整性,开发人员只会造成问题;)
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)
如果重复行的所有列都是相同的,那么下面的查询可以用来删除重复的记录。
SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable
使用CTE。这个想法是连接一个或多个列,形成一个重复的记录,然后删除你喜欢的:
;with cte as (
select
min(PrimaryKey) as PrimaryKey
UniqueColumn1,
UniqueColumn2
from dbo.DuplicatesTable
group by
UniqueColumn1, UniqueColumn1
having count(*) > 1
)
delete d
from dbo.DuplicatesTable d
inner join cte on
d.PrimaryKey > cte.PrimaryKey and
d.UniqueColumn1 = cte.UniqueColumn1 and
d.UniqueColumn2 = cte.UniqueColumn2;
我更喜欢子查询\having count(*) > 1解决方案内部连接,因为我发现它更容易阅读,它很容易变成一个SELECT语句来验证什么将被删除,然后再运行它。
--DELETE FROM table1
--WHERE id IN (
SELECT MIN(id) FROM table1
GROUP BY col1, col2, col3
-- could add a WHERE clause here to further filter
HAVING count(*) > 1
--)