MySQL：查找跨多个字段的重复 [英] MySQL: finding duplicates across multiple fields

问题描述

假设我有一个MySQL数据库，表中有以下五个记录：

  ID：1 
 Field1：A 
 Field2：B 
 Field3：C 
 
 ID：2 
 Field1：D 
 Field2：E 
 Field3：F 
 
 ID：3 
 Field1：A 
 Field2：H 
 Field3：I 
 
 ID：4 
 Field1：J 
 Field2：K 
 Field3：A 
 
 ID：5 
 Field1：M 
 Field2：D 
 Field3：O 
  

请注意以下值是重复的：

ID 1，字段1具有与ID 3，字段1和ID 4，字段3相同的值。

ID 2，字段1具有与ID 5相同的值，字段2。

有没有可以找到所有上述重复的SELECT语句？

解决方案

它是可行的，但不能确定是否比应用程序级别更有效：

您的表：

  mysql>从测试中选择* 
 + ---- + -------- + -------- + -------- + 
 | id | field1 | field2 | field3 | 
 + ---- + -------- + -------- + -------- + 
 | 1 | A | B | C | 
 | 2 | D | E | F | 
 | 3 | A | H |我| 
 | 4 | J | K | A | 
 | 5 | M | D | O | 
 + ---- + -------- + -------- + -------- + 
 5行集（0.00秒）
  

选择找到重复：

 的MySQL>选择count（value）为dupe_count，从（从选择field1作为测试联合的值，从测试联合中选择field2全部从测试中选择field3）作为tbl组按值具有count（value）> 1顺序1 desc 
 + ------------ + ------- + 
 | dupe_count |值| 
 + ------------ + ------- + 
 | 3 | A | 
 | 2 | D | 
 + ------------ + ------- + 
 2行集（0.00秒）
  / pre> 
 
 
基本上你将三列合并成一个，然后寻找重复
 
Let's say I had a MySQL database with the following five records in a table:
ID: 1
Field1: A
Field2: B
Field3: C

ID: 2
Field1: D
Field2: E
Field3: F

ID: 3
Field1: A
Field2: H
Field3: I

ID: 4
Field1: J
Field2: K
Field3: A

ID: 5
Field1: M
Field2: D
Field3: O
Notice that the following values are duplicated:

ID 1, field 1 has the same value as ID 3, field 1 and ID 4, field 3.

ID 2, field 1 has the same value as ID 5, field 2.

Is there a SELECT statement that could find all of the above duplicates?
解决方案
it is doable but not sure if it is any more efficient than just doing it at the application level:

your table:
mysql> select * from test;
+----+--------+--------+--------+
| id | field1 | field2 | field3 |
+----+--------+--------+--------+
|  1 | A      | B      | C      |
|  2 | D      | E      | F      |
|  3 | A      | H      | I      |
|  4 | J      | K      | A      |
|  5 | M      | D      | O      |
+----+--------+--------+--------+
5 rows in set (0.00 sec)
the select to find dupes:
mysql> select count(value) as dupe_count,value from (select field1 as value from test union all select field2 from test union all select field3 from test) as tbl group by value having count(value) > 1 order by 1 desc;
+------------+-------+
| dupe_count | value |
+------------+-------+
|          3 | A     |
|          2 | D     |
+------------+-------+
2 rows in set (0.00 sec)
basically you union the three columns into one then look for dupes

                        
这篇关于MySQL：查找跨多个字段的重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！