logo标签列表

如何在SQL Server中删除重复行？

sqlsql-server-2008duplicatessql-delete
578

如何删除没有唯一行ID的重复行？

我的表格是：

col1  col2 col3 col4 col5 col6 col7
john  1    1    1    1    1    1 
john  1    1    1    1    1    1
sally 2    2    2    2    2    2
sally 2    2    2    2    2    2

在去除重复项后，我希望保留以下内容：

john  1    1    1    1    1    1
sally 2    2    2    2    2    2
我尝试了一些查询，但我认为它们依赖于拥有行ID，因此我没有得到期望的结果。例如：
DELETE
FROM table
WHERE col1 IN (
    SELECT id
    FROM table
    GROUP BY id
    HAVING (COUNT(col1) > 1)
)
9
这不是第一个链接的重复。在这个问题中没有行ID，而在链接的问题中有行ID。非常不同。 - Alien Technology
将以下程序相关内容从英语翻译成中文。仅返回翻译后的文本：更改“SELECT id FROM table GROUP BY id HAVING”以具有聚合函数，例如MAX/MIN，并使其工作。 - messed-up
29个回答
981

我喜欢使用CTEs和ROW_NUMBER，这两者的组合可以让我们看到哪些行被删除（或更新），因此只需将DELETE FROM CTE...更改为SELECT * FROM CTE：

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

演示（结果不同，我假设这是由于你的一处拼写错误）

COL1    COL2    COL3    COL4    COL5    COL6    COL7
john    1        1       1       1       1       1
sally   2        2       2       2       2       2

由于 PARTITION BY col1，这个示例通过一个单列col1来确定重复项。如果您想要包括多个列，只需将它们添加到PARTITION BY即可：

ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
2
@omachu23：在这种情况下，无论如何都没关系，尽管我认为在CTE中比在外部(AND COl1='John')更高效。通常情况下，你应该在CTE中应用过滤器。 - Tim Schmelter
1
最简单的解决方案可能只是 set rowcount 1 delete from t1 where col1=1 and col2=1，如此处所示。 - Zorgarath
22
此答案仅会删除列1中存在重复值的行。将“select”中的列添加到“partition by”中，例如使用答案中的select语句：RN = ROW_NUMBER()OVER(PARTITION BY col1,col2,col3,col4,col5,col6,col7 ORDER BY col1)。 - rlee
2
当我把CTE放进去时，我会得到SQL错误。那CTE是什么意思？ - Whitecat
1
在运行此查询之前，请先备份...否则您可能会后悔。 - Thomas
显示剩余11条评论
205

我建议使用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)
5
我有点困惑。您是从CTE中删除它而不是原始表格。这样如何运作？ - Bigeyes
24
从CTE中删除记录将从实际物理表中删除相应的记录。(因为CTE包含对实际记录的引用)。 - Shamseer K
2
直到看了这篇文章，我才意识到这一点...谢谢。 - Zakk Diaz
3
为什么您要删除原始文件和它的副本？我不理解为什么您不只删除副本，保留原始文件。 - Rich
2
错误：关系“cte”不存在。 - mishadr
显示剩余3条评论
83

不使用CTE和ROW_NUMBER()，您只需使用MAX函数与GROUP BY即可删除记录。以下是一个示例：

DELETE
FROM MyDuplicateTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyDuplicateTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
4
这个查询将会删除非重复的记录。 - Derek Smalls
9
好的，谢谢。@DerekSmalls 这并没有删除我的非重复记录。 - monteirobrena
3
或者您可以使用 MIN(ID) 保留原始记录。 - Savage
2
虽然这在许多情况下可能有效，但问题明确说明没有唯一的标识符。 - CervEd
选择 max(id) 也会返回非重复记录。因此，从删除中排除这些 ID 应该可以正常工作。 - Giannis Tzagarakis
显示剩余2条评论
36
如果您没有引用，如外键，您可以这样做。当测试概念证明并且测试数据被复制时，我经常这样做。
SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]

INTO [newTable]

FROM [oldTable]

进入对象资源管理器并删除旧表。

将新表重命名为旧表的名称。

1
这是我在入门材料中学习并使用的最简单的方法。 - eric
2
好奇当[oldTable]有数十亿行时，这个答案如何表现得很好... - cow
2
这将消耗固态硬盘的TBW，不建议使用。 - CodeGuru
1
请注意，当进行重命名操作时，表上的权限将会丢失。 - gordon613
23

移除所有重复项，但保留最小ID的第一个

在其他SQL服务器上也应该能够正常工作，例如Postgres：

DELETE FROM table
WHERE id NOT IN (
   select min(id) from table
   group by col1, col2, col3, col4, col5, col6, col7
)
1
“id”列是什么？OP的数据集中没有“id”。 - NullPumpkinException
3
@SergeMerzliakov，它是行的主键。当没有唯一键时，这个答案应该不起作用……然而，大多数读者在一般情况下都有唯一键，因此id对他们来说应该是有意义的。 - epox
2
即使没有明确的id列，您也可以使用自动生成的ctid PostgreSQL行标识符：https://dev59.com/_2Uq5IYBdhLWcg3wSOfS 在SQLite中，它被称为rowid：https://dev59.com/8Wsy5IYBdhLWcg3w-i7h - Ciro Santilli OurBigBook.com
18
DELETE from search
where id not in (
   select min(id) from search
   group by url
   having count(*)=1

   union

   SELECT min(id) FROM search
   group by url
   having count(*) > 1
)
1
你能否重写成：where id in (select max(id) ... having count(*) > 1)？ - Brent
2
我认为没有必要使用having或union，以下语句就足够了：delete from search where id not in (select min(id) from search group by url)。 - Christopher Yang
16

在 mysql 中有两种解决方案：

A) 使用 DELETE JOIN 语句删除重复行。

DELETE t1 FROM contacts t1
INNER JOIN contacts t2 
WHERE 
    t1.id < t2.id AND 
    t1.email = t2.email;

这个查询在引用contacts表两次，因此使用了表别名t1和t2。

结果为:

1 查询成功，影响了 4 行 (0.10 秒)

如果您想删除重复行并保留最低的id，可以使用以下语句：

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 
WHERE
    c1.id > c2.id AND 
    c1.email = c2.email;

   

B) 使用中间表删除重复行

以下是使用中间表删除重复行的步骤：

    1. 创建一个新表，其结构与要删除重复行的原始表相同。

    2. 将原始表中唯一的数据插入到中间表中。

    3. 删除原始表中所有重复的行。

 

第一步。创建一个结构与原始表相同的新表：

CREATE TABLE source_copy LIKE source;

第二步。将源表中不同的行插入到新表中：

INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values

第三步：删除原始表并将立即表重命名为原始表

DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;

来源: http://www.mysqltutorial.org/mysql-delete-duplicate-rows/

1
我的表格中大约有190000行数据。 对于这么多行的数据，解决方案1并不是一个好选择。 解决方案2适合我。谢谢。 - Nirav Chavda
11

请也查看下面的删除方式。

Declare @table table
(col1 varchar(10),col2 int,col3 int, col4 int, col5 int, col6 int, col7 int)
Insert into @table values 
('john',1,1,1,1,1,1),
('john',1,1,1,1,1,1),
('sally',2,2,2,2,2,2),
('sally',2,2,2,2,2,2)

创建了一个名为@table的样例表，并用给定的数据加载它。

输入图片描述

Delete  aliasName from (
Select  *,
        ROW_NUMBER() over (Partition by col1,col2,col3,col4,col5,col6,col7 order by col1) as rowNumber
From    @table) aliasName 
Where   rowNumber > 1

Select * from @table

在此输入图像描述

注意：如果您在 Partition by 部分中提供了所有列，则 order by 没有太多意义。

我知道，这个问题是三年前提出的，我的答案是 Tim 发布的另一个版本，但是我还是发帖，以防对任何人有所帮助。

这个更可靠 - Rouzbeh Zarandi
10

要从SQL Server的表中删除重复行，您需要按照以下步骤操作：

  1. 使用GROUP BY子句或ROW_NUMBER()函数查找重复行。
  2. 使用DELETE语句删除重复行。

设置样例表

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts(
    contact_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name NVARCHAR(100) NOT NULL,
    last_name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) NOT NULL,
);

插入数值

INSERT INTO contacts
    (first_name,last_name,email) 
VALUES
    ('Syed','Abbas','syed.abbas@example.com'),
    ('Catherine','Abel','catherine.abel@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Pilar','Ackerman','pilar.ackerman@example.com');

在此输入图片描述

查询


    SELECT 
   contact_id, 
   first_name, 
   last_name, 
   email
FROM 
   contacts;

从数据表中删除重复行
   WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        contacts
)
DELETE FROM cte
WHERE row_num > 1;

现在应该删除该记录

在这里输入图片描述

9

在sql server中有多种方法可以完成此操作，其中最简单的方法是： 将重复行表中不同的行插入新的临时表。然后从重复行表中删除所有数据，再将来自没有重复项的临时表的所有数据插入，如下所示。

select distinct * into #tmp From table
   delete from table
   insert into table
   select * from #tmp drop table #tmp

   select * from table

使用公共表表达式(CTE)删除重复行
With CTE_Duplicates as 
(select id,name , row_number() 
over(partition by id,name order by id,name ) rownumber  from table  ) 
delete from CTE_Duplicates where rownumber!=1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接