mysql查找重复元素

需求

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱

table and recording

CREATE TABLE `Person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> select * from Person;
+----+----------+
| id | email    |
+----+----------+
|  1 | a@b.com  |
|  2 | a@b.com  |
|  3 |  c@d.com |
+----+----------+
3 rows in set (0.00 sec)

解决

方法一（双表联合查询，id不同，email相同）

SELECT DISTINCT
	a.email AS Email