如何删除重复的行？

假设没有空值，您将 GROUP BY 唯一列和 SELECT MIN (or MAX) RowId 作为要保留的行。然后，只需删除没有行 id 的所有内容：

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

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

MIN(RowId)

和

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

---

另一种可能的方法是

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

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

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

执行计划

执行计划通常比公认答案中的执行计划更简单、更有效，因为它不需要自联接。

https://i.stack.imgur.com/ZJiWF.jpg

然而，情况并非总是如此。可能首选 GROUP BY 解决方案的一个地方是选择 hash aggregate 而不是流聚合的情况。

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

https://i.stack.imgur.com/iUlWm.jpg

可能有利于散列聚合方法的因素是

分区列上没有有用的索引

相对较少的组，每组中的重复项相对较多

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

---

Microsoft 支持网站上有一篇关于 removing duplicates 的好文章。这是相当保守的——他们让你在单独的步骤中完成所有事情——但它应该适用于大桌子。

我过去曾使用自联接来执行此操作，尽管它可能会被 HAVING 子句修饰：

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

---

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

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

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

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC 

---

delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid

Postgres：

delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid

---

DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 

---

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

DELETE
FROM
    Mytable
WHERE
    RowID NOT IN (
        SELECT
            MIN(RowID)
        FROM
            Mytable
        GROUP BY
            Col1,
            Col2,
            Col3
    )

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

---

我更喜欢 CTE 从 sql server 表中删除重复的行

强烈建议关注这篇文章 ::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 RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

不保留原创

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

---

要获取重复行：

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);      

---

Quick and Dirty 删除完全重复的行（对于小表）：

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

---

我更喜欢内部联接的 subquery\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
--)

---

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable

---

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

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

PS：在处理这样的事情时，我总是使用事务，这不仅可以确保所有内容都作为一个整体执行，还可以让我在不冒任何风险的情况下进行测试。但是当然，您无论如何都应该进行备份以确保...

---

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

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

它在 30 秒内从 2M 的表中删除了 1M 行（50% 重复）

---

使用 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;

---

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

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

考虑以下数据：

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    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) 

---

用这个

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

---

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

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

---

这是关于 removing duplicates 的另一篇好文章。

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

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

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

---

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

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

---

行，可以。使用临时表。如果你想要一个“有效”的单一的、性能不是很好的声明，你可以使用：

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。因此，您最终会得到一个表示“原始”非重复行的 RowID 列表。

---

另一种方法是创建一个具有相同字段和唯一索引的新表。然后将所有数据从旧表移动到新表。自动 SQL SERVER 忽略（如果会有重复值，还有一个选项：忽略、中断或 sth）重复值。所以我们有同一张表，没有重复的行。如果您不想要唯一索引，则在传输数据后可以将其删除。

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

---

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

---

创建具有相同结构的新空白表 执行如下查询 INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count(*) > 1 然后执行此查询 INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count( *) = 1

---

另一种方法：-

DELETE A
FROM   TABLE A,
       TABLE B
WHERE  A.COL1 = B.COL1
       AND A.COL2 = B.COL2
       AND A.UNIQUEFIELD > B.UNIQUEFIELD 

---

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

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0

---

从应用程序级别（不幸的是）。我同意防止重复的正确方法是在数据库级别通过使用唯一索引，但在 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) 吗？

---

DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

---

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)

---

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

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

---