删除重复行的SQL查询

---

在使用数据库避免重复数据时，我们应该在创建数据库表时遵循某些实践。

• 定义主键以识别行簇和非簇索引。
• 使用约束来提高数据完整性和性能。

即使遵循最佳实践，数据库表也可能包含重复行。这些重复行在从数据库检索数据时会造成问题。

因此，我们必须确保数据库行唯一。为此，首先我们需要验证表中是否存在重复行，如果存在重复行，则必须通过更改表数据来删除它们。本文将讨论从表中删除重复行的不同方法。

• 使用GroupBy和Having子句
• 使用公用表表达式 (CTE)
• 使用RANK()函数

创建数据库表

首先，让我们使用CREATE TABLE语句在SQL Server中创建一个名为Author的表。

CREATE TABLE Author(
   [ID] INT identity(1,1),
   [FirstName] Varchar(100),
   [LastName] Varchar(100),
   [Country] Varchar(100)
)

现在，让我们向Author表中插入值。

INSERT INTO Author([FirstName], [LastName], [Country]) values('Mithlesh', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Mithlesh', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Mithlesh', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Rudra', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Rudra', 'Upadhyay', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Chandan', 'Gautam', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Chandan', 'Gautam', 'India')
INSERT INTO Author([FirstName], [LastName], [Country]) values('Chandan', 'Gautam', 'India')

这些值将如下所示插入到Author表中。我们可以使用以下查询检查此Author表及其行：

SELECT * FROM Author

输出将如下所示。请注意，该表包含如下所示的重复行：

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
2	Mithlesh	Upadhyay	India
3	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
5	Rudra	Upadhyay	India
6	Chandan	Gautam	India
7	Chandan	Gautam	India
8	Chandan	Gautam	India

现在，我们将编写不同的查询以从上面的Author表中删除重复行。

使用GroupBy和Having子句

我们可以使用GROUP BY子句识别重复行。它根据列对行进行分组。我们将选择FirstName、LastName和Country来分组行，然后从每个组中选择一行。它将返回Author表中的唯一行。

DELETE FROM Author
WHERE ID NOT IN (
   SELECT MIN(ID)
   FROM Author
   GROUP BY FirstName, LastName, Country
   HAVING COUNT(*) > 1
)

上述查询后Author表的内容。请注意，由于重复的值，行号2、3、5、7和8被删除了。

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
6	Chandan	Gautam	India

使用公用表表达式 (CTE)

我们还可以使用公用表表达式 (CTE) 来删除SQL Server中的重复行。CTE是在SQL Server 2005中引入的。我们将编写SQL ROW_NUMBER()函数。它为行添加一个唯一的顺序行号。我们使用Partition by子句使用FirstName、LastName和Country为每一行生成一个行号。

请注意，我们有初始的包含重复行的表，因此我们可以应用此SQL来删除重复行并返回原始表作为输出。

WITH CTE AS (
   SELECT ID, FirstName, LastName, Country, ROW_NUMBER() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS row_num FROM Author
)
SELECT * FROM CTE

它将添加另一列“row_num”。此列将显示重复行出现的次数，如下所示：

ID	FirstName	LastName	Country	row_num
6	Chandan	Gautam	India	1
1	Mithlesh	Upadhyay	India	1
4	Rudra	Upadhyay	India	1

现在，我们将删除所有row_num大于1的行。因此，它将保留唯一行。您可以使用以下查询执行此操作：

WITH CTE AS ( 
   SELECT ID, FirstName, LastName, Country, ROW_NUMBER() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS row_num FROM Author
)
DELETE FROM Author WHERE ID IN ( 
   SELECT ID FROM CTE WHERE row_num > 1
)

它将从Author表中删除所有重复行。您可以使用以下查询检查此更新后的表：

SELECT * FROM Author

输出将是：

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
6	Chandan	Gautam	India

使用RANK()函数

我们还可以使用RANK函数删除SQL中的重复行。此方法类似于上面使用ROW_NUMBER()的方法。RANK()函数显示重复行的值。

请注意，我们有初始的包含重复行的表，因此我们可以应用此SQL来删除重复行并返回原始表作为输出。

WITH CTE AS ( 
   SELECT ID, FirstName, LastName, Country, RANK() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS rank_num FROM Author
)
SELECT * FROM CTE

请注意，这与上述方法相同，但此处我们使用RANK()代替ROW_NUMBER()函数，因此列名为rank_num而不是row_num。从Author表中删除重复行的最终查询是：

WITH CTE AS ( 
   SELECT ID, FirstName, LastName, Country, RANK() 
   OVER (PARTITION BY FirstName, LastName, Country ORDER BY ID) 
   AS rank_num FROM Author
)
DELETE FROM Author WHERE ID IN ( 
   SELECT ID FROM CTE WHERE rank_num > 1
)

它将从Author表中删除所有重复行。上述查询后Author表的内容如下：

ID	FirstName	LastName	Country
1	Mithlesh	Upadhyay	India
4	Rudra	Upadhyay	India
6	Chandan	Gautam	India

结论

在本文中，我们讨论了三种从SQL数据库表中删除重复行的不同方法。我们在第一种方法中使用group by和having子句。

第一种方法在SQL中编写起来最简单。我们在第二种和第三种方法中分别使用了ROW_NUMBER()和RANK()函数。最后两种方法彼此相似，因此您可以很容易地理解它们。

常见问题 (FAQ)

为什么要从表中删除重复行？

我们从表中删除重复行，因为重复行会造成数据完整性问题。具有唯一行的表始终确保数据的完整性、性能和查询的计算。

在SQL中使用ROW_NUMBER()和RANK()函数有什么区别？

ROW_NUMBER()和RANK()函数都在分区内为行分配数字。ROW_NUMBER()为每一行分配一个唯一的数字。而RANK()为相同的行分配相同的排名，并跳过后续的数字。

何时使用GROUP BY和HAVING删除重复项而不是CTE？

区别不大，但这种方法更容易编写和理解。