SQL查找删除重复的数据行

发布时间:2020-04-28编辑:脚本学堂
本文介绍下,使用sql语句查找并删除重复行的方法,在数据库的维护管理中,对于重复的数据需要定期清理。有需要的朋友参考下吧。

本节内容:
查找并删除重复行的方法。

一,如何查找重复行
第一步是定义什么样的行才是重复行。
多数情况下很简单:它们某一列具有相同的值。

本文采用这一定义,或许你对“重复”的定义比这复杂,你需要对sql做些修改。

要用到的数据样本:
 

复制代码 代码示例:
create table test(id int not null primary key, day date not null); 
 
insert into test(id, day) values(1, '2006-10-08'); 
insert into test(id, day) values(2, '2006-10-08'); 
insert into test(id, day) values(3, '2006-10-09'); 
 
select * from test; 
+----+------------+ 
| id | day        | 
+----+------------+ 
|  1 | 2006-10-08 | 
|  2 | 2006-10-08 | 
|  3 | 2006-10-09 | 
+----+------------+ 

前面两行在day字段具有相同的值,因此如何将他们当做重复行,这里有一查询语句可以查找。
查询语句使用GROUP BY子句把具有相同字段值的行归为一组,然后计算组的大小。
例句:
 

复制代码 代码示例:
 select day, count(*) from test GROUP BY day; 
+------------+----------+ 
| day        | count(*) | 
+------------+----------+ 
| 2006-10-08 |        2 | 
| 2006-10-09 |        1 | 
+------------+----------+ 

重复行的组大小大于1。
如何希望只显示重复行,必须使用HAVING子句,比如:
 

复制代码 代码示例:
select day, count(*) from test group by day HAVING count(*) > 1; 
+------------+----------+ 
| day        | count(*) | 
+------------+----------+ 
| 2006-10-08 |        2 | 
+------------+----------+ 

基本的技巧:根据具有相同值的字段分组,然后知显示大小大于1的组。

二,为什么不能使用WHERE子句?
因为WHERE子句过滤的是分组之前的行,HAVING子句过滤的是分组之后的行。

三,如何删除重复行
一个常见的任务是,重复行只保留一行,其他删除,然后可以创建适当的索引,防止以后再有重复的行写入数据库
同样,首先是弄清楚重复行的定义。
要保留的是哪一行呢?第一行,或者某个字段具有最大值的行?

本文中,假设要保留的是第一行——id字段具有最小值的行,意味着你要删除其他的行。

也许最简单的方法是通过临时表。

尤其对于mysql,有些限制是不能在一个查询语句中select的同时update一个表。
在另一篇文章 MySQL SELECT同时UPDATE同一张表 中,讲述了如何绕过这些限制。

简单起见,这里只用到了临时表的方法。

任务:删除所有重复行,除了分组中id字段具有最小值的行。

因此,需要找出大小大于1的分组,以及希望保留的行。
可以使用MIN()函数。这里的语句是创建临时表,以及查找需要用DELETE删除的行。

例句:
 

复制代码 代码示例:
create temporary table to_delete (day date not null, min_id int not null); 
 
insert into to_delete(day, min_id) 
   select day, MIN(id) from test group by day having count(*) > 1; 
 
select * from to_delete; 
+------------+--------+ 
| day        | min_id | 
+------------+--------+ 
| 2006-10-08 |      1 | 
+------------+--------+ 

有了这些数据,可以开始删除“脏数据”行了。
可以有几种方法,各有优劣,这里不做详细比较,只是说明在支持查询子句的关系数据库中,使用的标准方法。

例句:
 

复制代码 代码示例:
delete from test 
   where exists( 
      select * from to_delete 
      where to_delete.day = test.day and to_delete.min_id <> test.id 
   ) 

四,如何查找多列上的重复行

有人问过以下的问题:

一个表上有两个字段b和c,分别关联到其他两个表的b和c字段。我想要找出在b字段或者c字段上具有重复值的行。
咋看很难明白,通过对话后我理解了:他想要对b和c分别创建unique索引。

如上所述,查找在某一字段上具有重复值的行很简单,只要用group分组,然后计算组的大小。
并且查找全部字段重复的行也很简单,只要把所有字段放到group子句。但如果是判断b字段重复或者c字段重复,问题困难得多。

提问者用到的样本数据:
 

复制代码 代码示例:
create table a_b_c( 
   a int not null primary key auto_increment, 
   b int, 
   c int 
); 
 
insert into a_b_c(b,c) values (1, 1); 
insert into a_b_c(b,c) values (1, 2); 
insert into a_b_c(b,c) values (1, 3); 
insert into a_b_c(b,c) values (2, 1); 
insert into a_b_c(b,c) values (2, 2); 
insert into a_b_c(b,c) values (2, 3); 
insert into a_b_c(b,c) values (3, 1); 
insert into a_b_c(b,c) values (3, 2); 
insert into a_b_c(b,c) values (3, 3); 

现在,可以轻易看到表里面有一些重复的行,但找不到两行具有相同的二元组{b, c}。这就是为什么问题会变得困难了。

2,错误的查询语句

如果把两列放在一起分组,你会得到不同的结果,具体看如何分组和计算大小。
提问者恰恰是困在了这里。有时候查询语句找到一些重复行却漏了其他的。

这是他用到的查询:
 

复制代码 代码示例:
select b, c, count(*) from a_b_c 
group by b, c 
having count(distinct b > 1) 
   or count(distinct c > 1); 

结果返回所有的行,因为CONT(*)总是1.为什么?因为 >1 写在COUNT()里面。
这个错误很容易被忽略,事实上等效于:
 

复制代码 代码示例:
select b, c, count(*) from a_b_c 
group by b, c 
having count(1) 
   or count(1); 

为什么?因为(b > 1)是一个布尔值,根本不是想要的结果。

需要的是:
 

复制代码 代码示例:
select b, c, count(*) from a_b_c 
group by b, c 
having count(distinct b) > 1 
   or count(distinct c) > 1; 

返回空结果。

很显然,因为没有重复的{b,c}。
这人试了很多其他的OR和AND的组合,用来分组的是一个字段,计算大小的是另一个字段,像这样:
 

复制代码 代码示例:
select b, count(*) from a_b_c group by b having count(distinct c) > 1; 
+------+----------+ 
| b    | count(*) | 
+------+----------+ 
|    1 |        3 | 
|    2 |        3 | 
|    3 |        3 | 
+------+----------+ 

没有一个能够找出全部的重复行。
而且,对于某些情况,这种语句是有效的,如果错误地以为就是这么写法,然而对于另外的情况,很可能得到错误结果。
事实上,单纯用GROUP BY 是不可行的。
为什么?
因为当对某一字段使用group by时,就会把另一字段的值分散到不同的分组里。
对这些字段排序可以看到这些效果,正如分组做的那样。