1. 首页
  2. IT资讯

MySQL 如何查找删除重复行?

“u003Cpu003E优质文章,及时送达u003Cu002Fpu003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002FRcsUnd92eoboDR” img_width=”640″ img_height=”29″ alt=”MySQL 如何查找删除重复行?” inline=”0″u003Eu003Cpu003E作者 | 神父、我有罪u003Cu002Fpu003Eu003Cpu003E来源 | www.xaprbu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-1″u003E.comu003Cu002Fiu003Eu003Cu002Fpu003Eu003Cpu003E如何查找重复行u003Cu002Fpu003Eu003Cpu003E第一步是定义什么样的行才是重复行。多数情况下很简单:它们某一列具有相同的值。本文采用这一定义,或许你对“重复”的定义比这复杂,你需要对sql做些修改。本文要用到的数据样本:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Ecreate table test(id int not primary key, day date not );u003Cbru003Eu003Cbru003Einsert into test(id, day) values(1, ‘2006-10-08’);u003Cbru003Einsert into test(id, day) values(2, ‘2006-10-08’);u003Cbru003Einsert into test(id, day) values(3, ‘2006-10-09’);u003Cbru003Eu003Cbru003Eselect * from test;u003Cbru003E+—-+————+u003Cbru003E| id | day |u003Cbru003E+—-+————+u003Cbru003E| 1 | 2006-10-08 |u003Cbru003E| 2 | 2006-10-08 |u003Cbru003E| 3 | 2006-10-09 |u003Cbru003E+—-+————+u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E前面两行在day字段具有相同的值,因此如何我将他们当做重复行,这里有一查询语句可以查找。查询语句使用GROUP u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003EBYu003Cu002Fiu003E子句把具有相同字段值的行归为一组,然后计算组的大小。u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect day, count(*) from test GROUP u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003EBYu003Cu002Fiu003E day;u003Cbru003E+————+———-+u003Cbru003E| day | count(*) |u003Cbru003E+————+———-+u003Cbru003E| 2006-10-08 | 2 |u003Cbru003E| 2006-10-09 | 1 |u003Cbru003E+————+———-+u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E重复行的组大小大于1。如何希望只显示重复行,必须使用HAVING子句,比如u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect day, count(*) from test group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E day HAVING count(*) > 1;u003Cbru003E+————+———-+u003Cbru003E| day | count(*) |u003Cbru003E+————+———-+u003Cbru003E| 2006-10-08 | 2 |u003Cbru003E+————+———-+u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E这是基本的技巧:根据具有相同值的字段分组,然后知显示大小大于1的组。u003Cu002Fpu003Eu003Cpu003E为什么不能使用WHERE子句?因为WHERE子句过滤的是分组之前的行,HAVING子句过滤的是分组之后的行。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h1″u003E如何删除重复行u003Cu002Fh1u003Eu003Cpu003E一个相关的问题是如何删除重复行。一个常见的任务是,重复行只保留一行,其他删除,然后你可以创建适当的索引,防止以后再有重复的行写入数据库。u003Cu002Fpu003Eu003Cpu003E同样,首先是弄清楚重复行的定义。你要保留的是哪一行呢?第一行,或者某个字段具有最大值的行?本文中,假设要保留的是第一行——id字段具有最小值的行,意味着你要删除其他的行。u003Cu002Fpu003Eu003Cpu003E也许最简单的方法是通过临时表。尤其对于MYSQL,有些限制是不能在一个查询语句中select的同时update一个表。简单起见,这里只用到了临时表的方法。u003Cu002Fpu003Eu003Cpu003E我们的任务是:删除所有重复行,除了分组中id字段具有最小值的行。因此,需要找出大小大于1的分组,以及希望保留的行。你可以使用MIN函数。这里的语句是创建临时表,以及查找需要用DELETE删除的行。u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Ecreate temporary table to_delete (day date not , min_id int not );u003Cbru003Eu003Cbru003Einsert into to_delete(day, min_id)u003Cbru003Eselect day, MIN(id) from test group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E day having count(*) > 1;u003Cbru003Eu003Cbru003Eselect * from to_delete;u003Cbru003E+————+——–+u003Cbru003E| day | min_id |u003Cbru003E+————+——–+u003Cbru003E| 2006-10-08 | 1 |u003Cbru003E+————+——–+u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E有了这些数据,你可以开始删除“脏数据”行了。可以有几种方法,各有优劣(详见我的文章many-to-one problems in SQL),但这里不做详细比较,只是说明在支持查询子句的关系数据库中,使用的标准方法。u003Cu002Fpu003Eu003Cpu003Etips:欢迎u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-2″u003E关注u003Cu002Fiu003Eu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-2″u003E微信u003Cu002Fiu003E公众号:Java后端,获取每日技术博文推送。u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Edelete from testu003Cbru003Ewhere eu003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-2″u003Exiu003Cu002Fiu003Ests(u003Cbru003Eselect * from to_deleteu003Cbru003Ewhere to_delete.day = test.day and to_delete.min_id <> test.idu003Cbru003E)u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h1″u003E如何查找多列上的重复行u003Cu002Fh1u003Eu003Cpu003E有人最近问到这样的问题:我的一个表上有两个字段b和c,分别关联到其他两个表的b和c字段。我想要找出在b字段或者c字段上具有重复值的行。u003Cu002Fpu003Eu003Cpu003E咋看很难明白,通过对话后我理解了:他想要对b和c分别创建unique索引。如上所述,查找在某一字段上具有重复值的行很简单,只要用group分组,然后计算组的大小。并且查找全部字段重复的行也很简单,只要把所有字段放到group子句。但如果是判断b字段重复或者c字段重复,问题困难得多。这里提问者用到的样本数据u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Ecreate table a_b_c(u003Cbru003Ea int not primary key auto_increment,u003Cbru003Eb int,u003Cbru003Ec intu003Cbru003E);u003Cbru003Eu003Cbru003Einsert into a_b_c(b,c) values (1, 1);u003Cbru003Einsert into a_b_c(b,c) values (1, 2);u003Cbru003Einsert into a_b_c(b,c) values (1, 3);u003Cbru003Einsert into a_b_c(b,c) values (2, 1);u003Cbru003Einsert into a_b_c(b,c) values (2, 2);u003Cbru003Einsert into a_b_c(b,c) values (2, 3);u003Cbru003Einsert into a_b_c(b,c) values (3, 1);u003Cbru003Einsert into a_b_c(b,c) values (3, 2);u003Cbru003Einsert into a_b_c(b,c) values (3, 3);u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E现在,你可以轻易看到表里面有一些重复的行,但找不到两行具有相同的二元组{b, c}。这就是为什么问题会变得困难了。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h1″u003E错误的查询语句u003Cu002Fh1u003Eu003Cpu003E如果把两列放在一起分组,你会得到不同的结果,具体看如何分组和计算大小。提问者恰恰是困在了这里。有时候查询语句找到一些重复行却漏了其他的。这是他用到了查询u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect b, c, count(*) from a_b_cu003Cbru003Egroup u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E b, cu003Cbru003Ehaving count(distinct b > 1)u003Cbru003Eor count(distinct c > 1);u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E结果返回所有的行,因为CONT(*)总是1.为什么?因为 >1 写在COUNT里面。这个错误很容易被忽略,事实上等效于u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect b, c, count(*) from a_b_cu003Cbru003Egroup u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E b, cu003Cbru003Ehaving count(1)u003Cbru003Eor count(1);u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E为什么?因为(b > 1)是一个布尔值,根本不是你想要的结果。你要的是u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect b, c, count(*) from a_b_cu003Cbru003Egroup u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E b, cu003Cbru003Ehaving count(distinct b) > 1u003Cbru003Eor count(distinct c) > 1;u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E返回空结果。很显然,因为没有重复的{b,c}。这人试了很多其他的OR和AND的组合,用来分组的是一个字段,计算大小的是另一个字段,像这样u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect b, count(*) from a_b_c group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E b having count(distinct c) > 1;u003Cbru003E+——+———-+u003Cbru003E| b | count(*) |u003Cbru003E+——+———-+u003Cbru003E| 1 | 3 |u003Cbru003E| 2 | 3 |u003Cbru003E| 3 | 3 |u003Cbru003E+——+———-+u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E没有一个能够找出全部的重复行。而且最令人沮丧的是,对于某些情况,这种语句是有效的,如果错误地以为就是这么写法,然而对于另外的情况,很可能得到错误结果。u003Cu002Fpu003Eu003Cpu003E事实上,单纯用GROUP u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003EBYu003Cu002Fiu003E 是不可行的。为什么?因为当你对某一字段使用group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E时,就会把另一字段的值分散到不同的分组里。对这些字段排序可以看到这些效果,正如分组做的那样。首先,对b字段排序,看看它是如何分组的u003Cu002Fpu003Eu003Cimg src=”http:u002Fu002Fp1.pstatp.comu002Flargeu002Fpgc-imageu002FRg4t2aU6pwXeRr” img_width=”1071″ img_height=”425″ alt=”MySQL 如何查找删除重复行?” inline=”0″u003Eu003Cpu003E当你对b字段排序(分组),相同值的c被分到不同的组,因此不能用COUNT(DISTINCT c)来计算大小。COUNT之类的内部函数只作用于同一个分组,对于不同分组的行就无能为力了。类似,如果排序的是c字段,相同值的b也会分到不同的组,无论如何是不能达到我们的目的的。u003Cu002Fpu003Eu003Cpu003Eu003Cu002Fpu003Eu003Ch1 toutiao-origin=”h1″u003E几种正确的方法u003Cu002Fh1u003Eu003Cpu003E也许最简单的方法是分别对某个字段查找重复行,然后用UNION拼在一起,像这样:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect b as value, count(*) as cnt, ‘b’ as what_colu003Cbru003Efrom a_b_c group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E b having count(*) > 1u003Cbru003Eunionu003Cbru003Eselect c as value, count(*) as cnt, ‘c’ as what_colu003Cbru003Efrom a_b_c group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E c having count(*) > 1;u003Cbru003E+——-+—–+———-+u003Cbru003E| value | cnt | what_col |u003Cbru003E+——-+—–+———-+u003Cbru003E| 1 | 3 | b |u003Cbru003E| 2 | 3 | b |u003Cbru003E| 3 | 3 | b |u003Cbru003E| 1 | 3 | c |u003Cbru003E| 2 | 3 | c |u003Cbru003E| 3 | 3 | c |u003Cbru003E+——-+—–+———-+u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E输出what_col字段为了提示重复的是哪个字段。另一个办法是使用嵌套查询:u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect a, b, c from a_b_cu003Cbru003Ewhere b in (select b from a_b_c group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E b having count(*) > 1)u003Cbru003Eor c in (select c from a_b_c group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E c having count(*) > 1);u003Cbru003E+—-+——+——+u003Cbru003E| a | b | c |u003Cbru003E+—-+——+——+u003Cbru003E| 7 | 1 | 1 |u003Cbru003E| 8 | 1 | 2 |u003Cbru003E| 9 | 1 | 3 |u003Cbru003E| 10 | 2 | 1 |u003Cbru003E| 11 | 2 | 2 |u003Cbru003E| 12 | 2 | 3 |u003Cbru003E| 13 | 3 | 1 |u003Cbru003E| 14 | 3 | 2 |u003Cbru003E| 15 | 3 | 3 |u003Cbru003E+—-+——+——+u003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E这种方法的效率要比使用UNION低许多,并且显示每一重复的行,而不是重复的字段值。还有一种方法,将自己跟group的嵌套查询结果联表查询。写法比较复杂,但对于复杂的数据或者对效率有较高要求的情况,是很有必要的。u003Cu002Fpu003Eu003Cpreu003Eu003Ccodeu003Eselect a, a_b_c.b, a_b_c.cu003Cbru003Efrom a_b_cu003Cbru003Eleft outer join (u003Cbru003Eselect b from a_b_c group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E b having count(*) > 1u003Cbru003E) as b on a_b_c.b = b.bu003Cbru003Eleft outer join (u003Cbru003Eselect c from a_b_c group u003Ci class=”chrome-extension-mutihighlight chrome-extension-mutihighlight-style-5″u003Ebyu003Cu002Fiu003E c having count(*) > 1u003Cbru003E) as c on a_b_c.c = c.cu003Cbru003Ewhere b.b is not or c.c is notu003Cu002Fcodeu003Eu003Cu002Fpreu003Eu003Cpu003E以上方法可行,我敢肯定还有其他的方法。如果UNION能用,我想会是最简单不过的了。u003Cu002Fpu003Eu003Cp class=”pgc-end-source”u003E-END-u003Cu002Fpu003E”

原文始发于:MySQL 如何查找删除重复行?

主题测试文章,只做测试使用。发布者:逗乐男神i,转转请注明出处:http://www.cxybcw.com/17850.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code