乐趣区

关于java:面试官MySQL-如何查找删除重复行我竟然写不出来

本文讲述如何查找数据库里反复的行。这是初学者非常广泛遇到的问题。办法也很简略。这个问题还能够有其余演变,例如,如何查找“两字段反复的行”(#mysql IRC 频道问到的问题)

如何查找反复行

第一步是定义什么样的行才是反复行。少数状况下很简略:它们某一列具备雷同的值。本文采纳这一定义,或者你对“反复”的定义比这简单,你须要对 sql 做些批改。本文要用到的数据样本:

create table test(id int not 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 一个表。简略起见,这里只用到了长期表的办法。

咱们的工作是:删除所有反复行,除了分组中 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 |  +------------+--------+

有了这些数据,你能够开始删除“脏数据”行了。能够有几种办法,各有优劣(详见我的文章 many-to-one problems in SQL),但这里不做具体比拟,只是阐明在反对查问子句的关系数据库中,应用的规范办法。

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}。这就是为什么问题会变得艰难了。

谬误的查问语句

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

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 时,就会把另一字段的值扩散到不同的分组里。对这些字段排序能够看到这些成果,正如分组做的那样。首先,对 b 字段排序,看看它是如何分组的

当你对 b 字段排序(分组),雷同值的 c 被分到不同的组,因而不能用 COUNT(DISTINCT c)来计算大小。COUNT()之类的外部函数只作用于同一个分组,对于不同分组的行就无能为力了。相似,如果排序的是 c 字段,雷同值的 b 也会分到不同的组,无论如何是不能达到咱们的目标的。

几种正确的办法

兴许最简略的办法是别离对某个字段查找反复行,而后用 UNION 拼在一起,像这样:

select b as value, count(*) as cnt, 'b' as what_col  from a_b_c group by b having count(*) > 1 union select c as value, count(*) as cnt, 'c' as what_col  from a_b_c group by c having count(*) > 1;  +-------+-----+----------+  | value | cnt | what_col |  +-------+-----+----------+  |   1 |  3 | b     |  |   2 |  3 | b     |  |   3 |  3 | b     |  |   1 |  3 | c     |  |   2 |  3 | c     |  |   3 |  3 | c     |  +-------+-----+----------+

输入 what_col 字段为了提醒反复的是哪个字段。另一个方法是应用嵌套查问:

select a, b, c from a_b_c  where b in (select b from a_b_c group by b having count(*) > 1)    or c in (select c from a_b_c group by c having count(*) > 1);  +----+------+------+  | a  | b   | c   | +----+------+------+  |  7 |   1 |   1 | |  8 |   1 |   2 | |  9 |   1 |   3 | | 10 |   2 |   1 | | 11 |   2 |   2 | | 12 |   2 |   3 | | 13 |   3 |   1 | | 14 |   3 |   2 | | 15 |   3 |   3 | +----+------+------+

这种办法的效率要比应用 UNION 低许多,并且显示每一反复的行,而不是反复的字段值。还有一种办法,将本人跟 group 的嵌套查问后果联表查问。写法比较复杂,但对于简单的数据或者对效率有较高要求的状况,是很有必要的。

select a, a_b_c.b, a_b_c.c  from a_b_c   left outer join (select b from a_b_c group by b having count(*) > 1  ) as b on a_b_c.b = b.b   left outer join (select c from a_b_c group by c having count(*) > 1  ) as c on a_b_c.c = c.c  where b.b is not null or c.c is not null

以上办法可行,我敢肯定还有其余的办法。如果 UNION 能用,我想会是最简略不过的了。

作者:愤恨的韭菜 \
起源:https://blog.csdn.net/zhengzh…\
原文:http://www.xaprb.com/blog/200…

近期热文举荐:

1.1,000+ 道 Java 面试题及答案整顿(2022 最新版)

2. 劲爆!Java 协程要来了。。。

3.Spring Boot 2.x 教程,太全了!

4. 别再写满屏的爆爆爆炸类了,试试装璜器模式,这才是优雅的形式!!

5.《Java 开发手册(嵩山版)》最新公布,速速下载!

感觉不错,别忘了顺手点赞 + 转发哦!

退出移动版