我需要从一个相当大的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 A
FROM TABLE A,
TABLE B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.UNIQUEFIELD > B.UNIQUEFIELD
其他回答
我想这会很有帮助。这里,ROW_NUMBER() OVER(分区由res1。Title ORDER BY res1.Id)作为num来区分重复的行。
delete FROM
(SELECT res1.*,ROW_NUMBER() OVER(PARTITION BY res1.Title ORDER BY res1.Id)as num
FROM
(select * from [dbo].[tbl_countries])as res1
)as res2
WHERE res2.num > 1
首先,您可以使用MIN()和Group By选择最小RowId。我们将保留这些行。
SELECT MIN(RowId) as RowId
FROM MyTable
GROUP BY Col1, Col2, Col3
删除那些不在最小RowId使用范围内的RowId
DELETE FROM MyTable WHERE RowId Not IN()
最后的查询:
DELETE FROM MyTable WHERE RowId Not IN(
SELECT MIN(RowId) as RowId
FROM MyTable
GROUP BY Col1, Col2, Col3
)
你也可以在SQL Fiddle中检查我的答案
如果重复行的所有列都是相同的,那么下面的查询可以用来删除重复的记录。
SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable
获取重复的行:
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING COUNT(*) > 1
删除重复的行。
DELETE users
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM users
GROUP BY name, email);
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