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
/ pre>
+ ------------ + ------- +
| dupe_count |值|
+ ------------ + ------- +
| 3 | A |
| 2 | D |
+ ------------ + ------- +
2行集(0.00秒)
基本上你将三列合并成一个,然后寻找重复
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屋!
查看全文