Let's say I had a MySQL database with the following five records in a table:
假设我有一个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
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 1,字段1具有与ID 3,字段1和ID 4,字段3相同的值。
ID 2, field 1 has the same value as ID 5, field 2.
ID 2,字段1具有与ID 5,字段2相同的值。
Is there a SELECT statement that could find all of the above duplicates?
是否有一个SELECT语句可以找到所有上述重复项?
1 个解决方案
#1
6
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
基本上你将三列合并成一个然后寻找欺骗
#1
6
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
基本上你将三列合并成一个然后寻找欺骗