MySQL 如何查找重复的值对

要在MySQL中查找重复的值对，请使用GROUP BY HAVING子句。

首先让我们创建一个表——

mysql> create table DemoTable748 (Id int, FirstName varchar(100));
Query OK, 0 rows affected (0.46 sec)

使用insert命令向表中插入一些记录——

mysql> insert into DemoTable748 values(100,'John');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable748 values(100,'Sam');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable748 values(200,'Carol');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable748 values(200,'Carol');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable748 values(300,'Mike');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable748 values(300,'Sam');
Query OK, 1 row affected (0.15 sec)

使用select语句从表中显示所有记录——

mysql> select *from DemoTable748;

将产生以下输出 –

+------+-----------+
| Id   | FirstName |
+------+-----------+
|  100 | John      |
|  100 | Sam       |
|  200 | Carol     |
|  200 | Carol     |
|  300 | Mike      |
|  300 | Sam       |
+------+-----------+
6 rows in set (0.00 sec)

以下是在MySQL中查找重复值对的查询 –

mysql> select Id from DemoTable748
   group by Id,FirstName having count(*)=2;

这将产生以下输出。由于Id和FirstName的唯一重复值对是200，因此输出显示相同的重复值对 –

+------+
| Id   |
+------+
| 200  | 
+------+
1 row in set (0.00 sec)

阅读更多：MySQL 教程