如何从SQL中的表中删除重复的行

有几种方法可以从SQL中的表中删除重复的行，例如，您可以使用临时表或类似于row_number（）的窗口函数来生成人工排名并删除重复的行。 通过使用临时表，您可以首先将所有唯一记录复制到临时表中，然后从原始表中删除所有数据，然后再次将唯一记录复制到原始表中。 这样，将删除所有重复的行，但是对于大型表，此解决方案将需要与原始表相同大小的额外空间。 第二种方法不需要额外的空间，因为它直接从表中删除重复的行。 它使用像row_number（）这样的排名函数为每行分配一个行号。

通过使用partition by子句，您可以重置特定列上的行号。 在这种方法中，所有唯一行的行号均为1，重复行的行号> 1，这使您可以轻松地删除这些重复行。 您可以通过使用公用表表达式 （请参阅T-SQL基础知识 ）或在Microsoft SQL Server上不使用它来实现。

毫无疑问，SQL查询是任何需要数据库和SQL知识的编程工作面试中必不可少的部分。 查询对于检查候选人的逻辑推理能力也非常有趣。

之前，我分享了访谈中常见的SQL查询列表，本文是对它的扩展。 在那篇文章中，我分享了很多基于SQL的良好问题，用户在评论中也分享了一些出色的问题，您应该看一下。

顺便说一句，这是另一个流行的SQL访谈问题的后续问题，您如何在表中找到重复的记录，我们已经在前面进行了讨论。 这是一个有趣的问题，因为许多候选人很容易混淆自己。

一些候选人说，他们将使用分组依据并打印计数超过1的名称来找到重复项，但是在删除此方法时不起作用，因为如果使用此逻辑删除，则重复行和唯一行都会被删除。

这点额外的细节，例如row_number，对于许多不每天使用SQL的程序员来说，使这个问题成为挑战。 现在，让我们看一下我们从 SQL Serve中删除表中重复行的解决方案

在探索解决方案之前，让我们首先创建表并填充测试数据以更好地理解问题和解决方案。 我使用临时表来避免在完成后将测试数据留在数据库中。 由于临时表会在您关闭与数据库的连接后立即清理，因此它们最适合测试。

在我们的表中，为简单起见，我只有一列，如果您有多个列，那么重复的定义取决于所有列应相等还是某些键列（例如，两个唯一的人的姓名和城市可以相同）。 在这种情况下，您需要通过在关键位置上使用这些列来扩展解决方案，例如，在第一个解决方案中使用不重复子句，在第二个解决方案中使用分区。

无论如何，这是我们的带有测试数据的临时表，它经过精心构造以具有重复项，您可以看到C ++在表中重复了两次，而Java在表中重复了两次。

-- create a temp table for testing
create table #programming (name varchar(10));

-- insert data with duplicate, C++ is repeated 3 times, while Java 2 times
insert into #programming values ('Java');
insert into #programming values ('C++');
insert into #programming values ('JavaScript');
insert into #programming values ('Python');
insert into #programming values ('C++');
insert into #programming values ('Java');
insert into #programming values ('C++');

-- cleanup
drop table #programming

是的，这是从表中删除重复元素的最简单但合乎逻辑的方法，它可在整个数据库（例如MySQL，Oracle或SQL Server）中使用。 这个想法是将唯一的行复制到临时表中。 您可以通过使用distingle子句找到唯一的行。 复制唯一行后，请删除原始表中的所有内容，然后再次复制唯一行。 这样，所有重复的行都被删除，如下所示。

-- removing duplicate using copy, delete and copy
select distinct name into #unique from #programming
delete from #programming;
insert into #programming select * from #unique

-- check after
select * from #programming

name
Java
C++
JavaScript
Python

您可以看到Java和C ++的重复出现已从#programming temp表中删除。 到目前为止，这是最简单的解决方案，也很容易理解，但是如果不进行练习就不会想到。 我建议从Joe Celko的经典著作《 SQL Puzzles and Answers ，第二版》中解决一些SQL难题，以提高您的SQL意识。 这是学习和掌握SQL逻辑的绝佳实践书。

row_number（）是SQL Server提供的几种排名函数之一，它也存在于Oracle数据库中 。 您可以使用此功能为行提供排名。 您可以进一步使用partition by来告诉SQL Server窗口是什么。 这样，一旦出现不同的名称，行号就会重新启动，但对于相同的名称，所有行都将获得顺序号，例如1、2、3等。现在，很容易在派生表中发现重复项，如下面的例子：

select * from (select *, row_number() OVER ( partition by name order by name) as rn from #programming) dups 
name rn
C++ 1
C++ 2
C++ 3
Java 1
Java 2
JavaScript 1
Python 1

现在，您可以删除所有重复项 ，除了rn> 1的行外，可以通过以下SQL查询完成：

delete dups 
from (select *, row_number() OVER ( partition by name order by name) as rn from #programming) 
dups 
WHERE rn > 1

(3 row(s) affected)

现在，如果您检查
#programming表再次不会有任何重复。

select * from #programming
name
Java
C++
JavaScript
Python

这是使用SQL从表中删除重复项的所有三种方法的摘要：

CTE代表公用表表达式，它类似于派生表，并用于在单个SELECT，INSERT，UPDATE，DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。 类似于派生表，CTE也不存储为对象，仅持续查询期间。 您可以使用CTE重写以前的解决方案，如下所示：

;with cte
as (select row_number() over (partition by name order by(select 0)) rn
from #programming)
delete from cte where rn > 1

逻辑与前面的示例完全相似，我使用的是select 0，因为在出​​现平局的情况下保留哪些行作为两个相同的数据是任意的。 如果您不熟悉CTE，那么我建议您阅读T-SQL基础知识 ，这是学习SQL Server基础知识的最佳书籍之一。

这就是如何从SQL的表中删除重复的行 。 就像我说的那样，这是常见的SQL查询之一，因此在进行编程工作面试时要做好准备。 我已经在SQL Server 2008中测试了查询，它们可以正常工作，您可能需要根据要使用的数据库（例如MySQL，Oracle或PostgreSQL）进行一些调整。 如果在删除Oracle，MySQL或任何其他数据库中的重复项时遇到任何问题，请随时发布。

面试中的其他常见SQL查询

• 如何在SQL中找到员工的第二高薪水？ （ 回答 ）
• 如何在一个SQL查询中联接三个表？ （ 解决方案 ）
• 如何在数据库中查找所有表名？ （ 查询 ）
• 如何使用SQL创建表备份或表副本？ （ 回答 ）
• 您如何找到从未订购过的所有客户？ （ 解决方案 ）
• 您可以使用row_number编写针对Oracle的分页查询吗？ （ 查询 ）
• 您如何使用相关查询找到员工的N最高薪水？ （ 解决方案 ）
• Joe Celko撰写的SQL拼图和答案（ 阅读 ）

翻译自: https://www.javacodegeeks.com/2016/07/remove-duplicate-rows-table-sql.html