如何删除重复的行？

在Microsoft支持站点上有一篇关于删除重复的好文章。这是相当保守的-他们让您以单独的步骤执行所有操作-但它应该可以很好地用于大型表。

我过去使用过自连接来执行此操作，尽管它可能会使用HAVING子句进行美化：

DELETE dupesFROM MyTable dupes, MyTable fullTableWHERE dupes.dupField = fullTable.dupFieldAND dupes.secondDupField = fullTable.secondDupFieldAND dupes.uniqueField > fullTable.uniqueField

这是一篇关于删除重复的好文章。

它讨论了为什么它很难：“SQL基于关系代数，关系代数中不允许出现重复，因为集合中不允许重复。”

临时表解决方案和两个mysql示例。

将来你会在数据库级别，还是从应用程序的角度来防止它。我建议数据库级别，因为你的数据库应该负责维护引用完整性，开发人员只会造成问题；）

假设没有空值，您GROUP BY唯一列，SELECTMIN (or MAX) RowId作为要保留的行。然后，删除所有没有行ID的内容：

DELETE FROM MyTableLEFT OUTER JOIN (SELECT MIN(RowId) as RowId, Col1, Col2, Col3FROM MyTableGROUP BY Col1, Col2, Col3) as KeepRows ONMyTable.RowId = KeepRows.RowIdWHEREKeepRows.RowId IS NULL

如果您有GUID而不是整数，您可以替换

MIN(RowId)

与

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

当然可以。使用临时表。如果你想要一个“有效”的单个、性能不太好的语句，你可以使用：

DELETE FROM MyTable WHERE NOT RowID IN(SELECT(SELECT TOP 1 RowID FROM MyTable mt2WHERE mt2.Col1 = mt.Col1AND mt2.Col2 = mt.Col2AND mt2.Col3 = mt.Col3)FROM MyTable mt)

基本上，对于表中的每一行，子选择查找与所考虑行完全相同的所有行的顶部RowID。因此，您最终会得到一个代表“原始”非重复行的RowID列表。

从应用程序级别（不幸的是），我同意防止重复的正确方法是在数据库级别通过使用唯一索引，但在SQLServer 2005中，索引只允许为900字节，而我的varchar（2048）字段将其吹走了。

我不知道它的性能如何，但我认为您可以编写一个触发器来强制执行此操作，即使您不能直接使用索引。类似于：

-- given a table stories(story_id int not null primary key, story varchar(max) not null)CREATE TRIGGER prevent_plagiarismON storiesafter INSERT, UPDATEASDECLARE @cnt AS INT
SELECT @cnt = Count(*)FROM   storiesINNER JOIN insertedON ( stories.story = inserted.storyAND stories.story_id != inserted.story_id )
IF @cnt > 0BEGINRAISERROR('plagiarism detected',16,1)
ROLLBACK TRANSACTIONEND

此外，varchar（2048）对我来说听起来很可疑（生活中有些事情是2048字节，但它很少见）；它真的不应该是varchar（max）吗？

1. 创建具有相同结构的新空白表

2. 像这样执行查询

   INSERT INTO tc_category1SELECT *FROM tc_categoryGROUP BY category_id, application_idHAVING count(*) > 1

3. Then execute this query

   INSERT INTO tc_category1SELECT *FROM tc_categoryGROUP BY category_id, application_idHAVING count(*) = 1

我有一个表，我需要保留不重复的行。我不确定速度或效率。

DELETE FROM myTable WHERE RowID IN (SELECT MIN(RowID) AS IDNo FROM myTableGROUP BY Col1, Col2, Col3HAVING COUNT(*) = 2 )

另一种可能的方法是

;
--Ensure that any immediately preceding statement is terminated with a semicolon aboveWITH cteAS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3ORDER BY ( SELECT 0)) RNFROM   #MyTable)DELETE FROM cteWHERE  RN > 1;

我使用上面的ORDER BY (SELECT 0)，因为在平局的情况下保留哪一行是任意的。

例如，要以RowID顺序保存最新的一个，您可以使用ORDER BY RowID DESC

执行计划

这方面的执行计划通常比接受的答案更简单，更有效，因为它不需要自连接。

然而，情况并非总是如此。首选GROUP BY解决方案的一个地方是优先选择哈希聚合而不是流聚合的情况。

ROW_NUMBER解决方案总是提供几乎相同的计划，而GROUP BY策略更灵活。

可能有利于哈希聚合方法的因素是

• 分区列上没有有用的索引
• 相对较少的组，每个组中的重复相对较多

在第二种情况的极端版本中（如果每个组中有很多重复项的组很少），也可以考虑简单地插入行以保留到新表中，然后TRUNCATE-ing原始并将它们复制回来以最小化日志记录，而不是删除非常高比例的行。

delete t1from table t1, table t2where t1.columnA = t2.columnAand t1.rowid>t2.rowid

职位：

deletefrom table t1using table t2where t1.columnA = t2.columnAand t1.rowid > t2.rowid

以下查询可用于删除重复行。此示例中的表以ID作为标识列，具有重复数据的列是Column1、Column2和Column3。

DELETE FROM TableNameWHERE  ID NOT IN (SELECT MAX(ID)FROM   TableNameGROUP  BY Column1,Column2,Column3/*Even if ID is not null-able SQL Server treats MAX(ID) as potentiallynullable. Because of semantics of NOT IN (NULL) including the clausebelow can simplify the plan*/HAVING MAX(ID) IS NOT NULL)

以下脚本显示了GROUP BY、HAVING、ORDER BY在一个查询中的用法，并返回具有重复列及其计数的结果。

SELECT YourColumnName,COUNT(*) TotalCountFROM   YourTableNameGROUP  BY YourColumnNameHAVING COUNT(*) > 1ORDER  BY COUNT(*) DESC

通过使用下面的查询，我们可以根据单列或多列删除重复记录。下面的查询是根据两列删除。表名是：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)

SELECT  DISTINCT *INTO tempdb.dbo.tmpTableFROM myTable
TRUNCATE TABLE myTableINSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTableDROP TABLE tempdb.dbo.tmpTable

我会提到这种方法，因为它可以帮助，并且适用于所有SQL服务器：通常只有一个-两个重复，并且ID和重复计数是已知的。在这种情况下：

SET ROWCOUNT 1 -- or set to number of rows to be deleteddelete from myTable where RowId = DuplicatedIDSET ROWCOUNT 0

快速和脏删除精确重复的行（对于小表）：

select  distinct * into t2 from t1;delete from t1;insert into t1 select *  from t2;drop table t2;

在粘贴这里的链接中可以找到另一个简单的解决方案。这个很容易掌握，似乎对大多数类似问题都有效。虽然它适用于SQL服务器，但使用的概念是完全可以接受的。

以下是链接页面的相关部分：

考虑这些数据：

EMPLOYEE_ID ATTENDANCE_DATEA001    2011-01-01A001    2011-01-01A002    2011-01-01A002    2011-01-01A002    2011-01-01A003    2011-01-01

那么我们如何删除这些重复的数据呢？

首先，使用以下代码在该表中插入一个标识列：

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)

使用以下代码来解决它：

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

这将删除重复的行，除了第一行

DELETEFROMMytableWHERERowID NOT IN (SELECTMIN(RowID)FROMMytableGROUP BYCol1,Col2,Col3)

参考（http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server）

另一种方式是创建一个新的表具有相同的字段和唯一索引。然后将所有数据从旧表移动到新表。自动SQLSERVER忽略（还有一个选项，如果有重复值该怎么办：忽略、中断或某物）重复值。所以我们有相同的表没有重复行。如果您不想要唯一索引，在传输数据后您可以删除它。

特别是对于较大的表，您可以使用DTS（SSIS包导入/导出数据）来快速将所有数据传输到您的新唯一索引表。对于700万行，只需几分钟。

DELETEFROMtable_name T1WHERErowid > (SELECTmin(rowid)FROMtable_name T2WHERET1.column_name = T2.column_name);

DELETEFROM MyTableWHERE NOT EXISTS (SELECT min(RowID)FROM MytableWHERE (SELECT RowIDFROM MytableGROUP BY Col1, Col2, Col3)));

我想我会分享我的解决方案，因为它在特殊情况下有效。在我的情况下，具有重复值的表没有外键（因为值是从另一个数据库复制的）。

begin transaction-- create temp table with identical structure as source tableSelect * Into #temp From tableName Where 1 = 2
-- insert distinct values into tempinsert into #tempselect distinct *from  tableName
-- delete from sourcedelete from tableName
-- insert into source from tempinsert into tableNameselect *from #temp
rollback transaction-- if this works, change rollback to commit and execute again to keep you changes!!

PS：当处理这样的事情时，我总是使用事务，这不仅确保了所有事情都作为一个整体执行，而且还允许我在没有任何风险的情况下进行测试。但是，当然，你无论如何都应该备份以确保…

对于内连接，我更喜欢使用subQuery\有coun（*）>1的解决方案，因为我发现它更容易阅读，并且很容易变成SELECT语句来验证在运行它之前会删除什么。

--DELETE FROM table1--WHERE id IN (SELECT MIN(id) FROM table1GROUP BY col1, col2, col3-- could add a WHERE clause here to further filterHAVING count(*) > 1--)

DELETE LUFROM   (SELECT *,Row_number()OVER (partition BY col1, col1, col3ORDER BY rowid DESC) [Row]FROM   mytable) LUWHERE  [row] > 1

使用CTE。这个想法是连接一个或多个形成重复记录的列，然后删除您喜欢的任何列：

;with cte as (selectmin(PrimaryKey) as PrimaryKeyUniqueColumn1,UniqueColumn2from dbo.DuplicatesTablegroup byUniqueColumn1, UniqueColumn1having count(*) > 1)delete dfrom dbo.DuplicatesTable dinner join cte ond.PrimaryKey > cte.PrimaryKey andd.UniqueColumn1 = cte.UniqueColumn1 andd.UniqueColumn2 = cte.UniqueColumn2;

这个查询对我来说显示了非常好的性能：

DELETE tblFROMMyTable tblWHEREEXISTS (SELECT*FROMMyTable tbl2WHEREtbl2.SameValue = tbl.SameValueAND tbl.IdUniqueValue < tbl2.IdUniqueValue)

它从2M表中删除了1M行（50%重复）

如果您想预览将要删除的行并控制要保留哪些重复行。请参阅http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

with MYCTE as (SELECT ROW_NUMBER() OVER (PARTITION BY DuplicateKey1,DuplicateKey2 -- optionalORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed) RNFROM MyTable)DELETE FROM MYCTEWHERE RN > 1

我更喜欢CTE从sql服务器表中删除重复的行

强烈建议遵循这篇文章::http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

通过保持原始

WITH CTE AS(SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RNFROM MyTable)
DELETE FROM CTE WHERE RN<>1

不保持原样

WITH CTE AS(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)FROM MyTable) DELETE CTEWHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

用这个

WITH tblTemp as(SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)As RowNumber,* FROM <table_name>)DELETE FROM tblTemp where RowNumber >1

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 )rankFrom MyTable)Twhere rank>1)
alter table MyTabledrop  column sno

现在让我们看看elasticalsearch表，这个表有重复的行，Id是相同的uniq字段。我们知道如果某个id存在于组条件中，那么我们可以删除该组范围外的其他行。我的方式显示了这个条件。

这个线程的许多案例都处于我的状态。只需根据您的案例更改您的目标组标准以删除重复（重复）行。

DELETEFROM elasticalsearchWHERE Id NOT IN(SELECT min(Id)FROM elasticalsearchGROUP BY FirmId,FilterSearchString)

欢呼

另一种方法：

DELETE AFROM   TABLE A,TABLE BWHERE  A.COL1 = B.COL1AND A.COL2 = B.COL2AND A.UNIQUEFIELD > B.UNIQUEFIELD

有时使用软删除机制，其中记录日期以指示已删除的日期。在这种情况下，可以使用UPDATE语句根据重复条目更新此字段。

UPDATE MY_TABLESET DELETED = getDate()WHERE TABLE_ID IN (SELECT x.TABLE_IDFROM MY_TABLE xJOIN (SELECT min(TABLE_ID) id, COL_1, COL_2, COL_3FROM MY_TABLE dGROUP BY d.COL_1, d.COL_2, d.COL_3HAVING count(*) > 1) AS d ON d.COL_1 = x.COL_1AND d.COL_2 = x.COL_2AND d.COL_3 = x.COL_3AND d.TABLE_ID <> x.TABLE_ID/*WHERE x.COL_4 <> 'D' -- Additional filter*/)

这种方法对于包含大约3000万行的相当温和的表非常有用。

这是删除重复记录的最简单方法

 DELETE FROM tblemp WHERE id IN(SELECT MIN(id) FROM tblempGROUP BY  title HAVING COUNT(id)>1)

获取重复行：

SELECTname, email, COUNT(*)FROMusersGROUP BYname, emailHAVING COUNT(*) > 1

删除重复行：

DELETE usersWHERE rowid NOT IN(SELECT MIN(rowid)FROM usersGROUP BY name, email);

我知道这个问题已经得到了回答，但我已经创建了非常有用的sp，它将为表重复创建一个动态删除语句：

    CREATE PROCEDURE sp_DeleteDuplicate @tableName varchar(100), @DebugMode int =1ASBEGINSET 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)ELSEBEGINDECLARE @i int =1DECLARE @Columns Varchar(max) ='';
WHILE (@i <= @MaxRow)BEGINSET @Columns=@Columns+(SELECT '['+name+'],' from #tableMatrix where rn = @i)
SET @i = @i+1;END
---DELETE LAST commaSET @Columns = LEFT(@Columns,LEN(@Columns)-1)
DECLARE @Sql nvarchar(max) = 'WITH cteRowsToDelteAS (SELECT ROW_NUMBER() OVER (PARTITION BY '+@Columns+' ORDER BY ( SELECT 0)) as rowNumber,* FROM '+@tableName+')
DELETE FROM cteRowsToDelteWHERE  rowNumber > 1;'SET NOCOUNT OFF;IF(@DebugMode = 1)SELECT @SqlELSEEXEC sp_executesql @SqlENDEND

因此，如果您创建这样的表：

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语句。

如果您需要排除任何列，只需在调试模式下运行它，获取代码并随意修改它。

我认为这会很有帮助。在这里，ROW_NUMBER（）OVER（PARTITION BY res1. title ORDER BY res1. Id）作为num被用来区分重复的行。

delete FROM(SELECT res1.*,ROW_NUMBER() OVER(PARTITION BY res1.Title ORDER BY res1.Id)as numFROM(select * from [dbo].[tbl_countries])as res1)as res2WHERE res2.num > 1

如果重复行中的所有列都相同，则可以使用下面的查询来删除重复记录。

SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTableTRUNCATE TABLE #OriginalTableINSERT INTO #OriginalTable SELECT * FROM #TemNewTableDROP TABLE #TemNewTable

对于表结构

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 t1FROM MyTable t1INNER JOIN MyTable t2WHERE t1.RowID > t2.RowIDAND t1.Col1 = t2.Col1AND t1.Col2=t2.Col2AND t1.Col3=t2.Col3;

我假设RowID是一种自动增量，其余的列有重复的值。

基于两列删除重复项的其他方法

我发现这个查询更容易阅读和替换。

DELETEFROMTABLE_NAMEWHERE FIRST_COLUMNSIN(SELECT * FROM( SELECT MIN(FIRST_COLUMNS)FROM TABLE_NAMEGROUP BYFIRST_COLUMNS,SECOND_COLUMNSHAVING COUNT(FIRST_COLUMNS) > 1) temp)

注意：在运行它之前最好simulate query。

在postgresql中删除表的重复行的一种非常简单的方法。

DELETE FROM table1 aUSING table1 bWHERE a.id < b.idAND a.column1 = b.column1AND a.column2 = b.column2;

首先，您可以使用MIN（）和Group By选择最小的RowId。我们将保留这些行。

   SELECT MIN(RowId) as RowIdFROM MyTableGROUP BY Col1, Col2, Col3

和Delete RowId的那些不在所选的最小RowId中使用

DELETE FROM MyTable WHERE RowId Not IN()

最终查询：

DELETE FROM MyTable WHERE RowId Not IN(
SELECT MIN(RowId) as RowIdFROM MyTableGROUP BY Col1, Col2, Col3)

你也可以查看我的答案SQL小提琴

删除重复记录

在这种情况下，大于运算符删除除第一条记录之外的所有记录

删除u1从用户u1加入用户u2u1.id>u2.idu1.email=u2.email

<在这种情况下，小于运算符删除除最后一条记录外的所有记录

删除u1从用户u1加入用户u2u1.id

创建另一个包含原始值的表：

CREATE TABLE table2 AS SELECT *, COUNT(*) FROM table1 GROUP BY name HAVING COUNT (*) > 0