程序员须知的五种快速删除数据库重复数据的方式

本文原文出处是C Sharp Corner ,为了更好地阅读,本人对 原文中不重要的语句有所删减.

当你在做数据库驱动的应用时, 你肯定知道怎么从数据库的表中移除重复数据.

→重复的记录不仅会占用大量空间, 而且会减慢数据库查询的执行过程.

含有重复数据的表

 我有一张叫"学生"的数据表,其中含有需要删除的重复数据. 表格如下

 

它有四列, 分别是:

1.Id - 主要的列(类型是 "int" 和  Identity[1,1]

2.Name - varchar(50) 和 NOT NULL

3.Age - int 和 NOT NULL

4.Standard - int he NOT NULL

你可以通过以下脚本在SQL服务器数据库中创建表格.

SET ANSI_NULLS ON    
GO    
    
SET QUOTED_IDENTIFIER ON    
GO    
    
SET ANSI_PADDING ON    
GO    
    
CREATE TABLE [dbo].[Student](    
    [Id] [int] IDENTITY(1,1) NOT NULL,    
    [Name] [varchar](50) NOT NULL,    
    [Age] [int] NOT NULL,    
    [Standard] [int] NOT NULL,    
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED     
(    
    [Id] ASC    
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
) ON [PRIMARY]    
    
GO    
    
SET ANSI_PADDING OFF    
GO    

我现在会通过运行以下的插入脚本来增加一些重复记录.

SET IDENTITY_INSERT [dbo].[Student] ON     
    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (1, N'John', 12, 5)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (2, N'Ram', 11, 4)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (3, N'Ram', 11, 4)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (4, N'Anna', 8, 3)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (5, N'Maya', 9, 4)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (6, N'Mary', 10, 5)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (7, N'Mary', 10, 5)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (8, N'Mary', 10, 6)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (9, N'Robin', 11, 5)    
GO    
INSERT [dbo].[Student] ([Id], [Name], [Age], [Standard]) VALUES (10, N'Donald', 12, 7)    
GO    
SET IDENTITY_INSERT [dbo].[Student] OFF    
GO   

现在打开表格就可以看到以下重复的数据了

重复的数据是叫以下名字的学生:

* Ram with id 2和3.

* Mary with id 6和7.

注意 Mary with id 8 并没有与其他id 6, 7 重复, 因为它的Standard与他们不同.

---------------------------------------------------------------------------------------------------------

方法一: 使用 Group By

SQL Group By语句按一栏或多栏对结果分组, 并且会搭配合计类的方法使用(COUNT, MAX, MIN, SUM, AVG).

我用Group By 和Min语句 建了一条SQL的查询.

SELECT MIN(Id) AS 'Id', Name, Age, Standard     
FROM Student     
GROUP BY Name, Age, Standard  

可以看到在上图的查询结果中, 重复项id 3和7不见了.

现在, 我可以在查询中用Delete语句删除这些重复项.

DELETE FROM STUDENT WHERE     
Id NOT IN(SELECT MIN(Id) AS 'Id' FROM Student GROUP BY Name, Age, Standard)    

运行后, 表格如下:

方法二: 使用 Distinct 语句

要使用Distinct语句(可返回无重复项), 你必须要建一个与原表结构和组成相同的新表.

然后将原表的所有非重复项插入到新表.

我建了一个新表叫Student1, 然后将所有非重复项从Student表加入到新表中, 查询语句如下:

Select  Distinct * into Student1 from Student;    

现在我要删除掉旧表Student, 将新表Student1重命名为Student. 使用一下查询语句:

DROP Table Student  
EXEC sp_rename 'Student1', 'Student'  

方法三: 借助编程语言

你也可以借助一些编程语言如C#, JavaScript, PHP等,来删除重复数据

我将使用 Entity Framework Core(一种C#编写的的 OR/M框架→[Object-Relational Mapping对象关系映射]). Entity Framework Core 广泛应用于ASP.NET Core数据库编程.

在Entity Framework Core中, 删除重复记录的代码如下:

using (var context = new MyContext())    
{    
    var students = context.Student.GroupBy(s => new { s.Name, s.Age, s.Standard }).SelectMany(grp => grp.Skip(1)); ;    
    context.Student.RemoveRange(students);    
    context.SaveChanges();    
}   

我使用了EF Core中的GroupBy()方法, 查找出了基于'Name, Age, Standard这三列的所有重复项.

然后我用RemoveRange()方法删除这些重复项.

方法四: 使用公用表表达式 CTE

CTE(Common table expression)是一个临时结果集, 是在单个SELECT、INSERT、UPDATE、DELETE 或CREATE VIEW 语句的执行范围内定义的.

我可以使用CTE和 "PARTITION BY"语句来删除重复项:

WITH myTemp    
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Name, Age, Standard ORDER BY Id) RN FROM Student)    
DELETE FROM myTemp WHERE RN > 1;  

注意: Row_Number()函数会根据在OVER子句中的选择的顺序, 生成连续的行号.

它将第一行设为1,并逐行递增.

我在Partition By 语句中放入了Student表的三列. 

当你在Partition By 语句中指定单列或数列时, 它会将结果集分割成多个部分.然后分级函数会在每个部分单独运行. 分级对每部分会单独计算, 都是从1开始.

===================================================

公用表达式的定义，包含三部分：

1.   公用表表达式的名字（在WITH之后）
2.   所涉及的列名（可选）
3.   一个SELECT语句(紧跟AS之后)

WITH expression_name [ ( column_name [,...n] ) ] 

AS 

( CTE_query_definition ) 

====================================================

方法五: 使用自连接Self Join

自连接是将自身表的一个镜像当作另一个表来对待, 在同一张表中的内连接.(Inner Join,又叫等值连接,返回两个表中联结字段相等的行,是Join的默认形式)

自连接也可以用于删除重复项.

1> "on"条件句检查重复栏

2> "where"条件句中比较表之间的主键栏

以下查询语句会查出Student表中的重复项:

Select * From Student s Inner Join Student s1     
On s.Name=s1.Name And s.Age=s1.Age And s.Standard=s1.Standard     
Where s.Id>s1.Id  

 下一步要做的就是执行Delete语句:

Delete Student where Id in(    
Select s.Id From Student s Inner Join Student s1     
On s.Name=s1.Name And s.Age=s1.Age And s.Standard=s1.Standard     
Where s.Id>s1.Id)