需求
编写一个 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
FROM
Person AS a,
Person AS b
WHERE
a.id <> b.id
AND a.email = b.email;
方法二（group by 分组，构建临时表，统计重复元素出现的次数）
临时表
SELECT
p.email AS Email,
COUNT( p.email ) AS num
FROM
Person AS p
GROUP BY
p.email;
查询 个数大于 1的元素
SELECT
t.Email AS Email
FROM
( SELECT p.email AS Email, COUNT( p.email ) AS num FROM Person AS p GROUP BY p.email ) AS t
WHERE
t.num > 1;
方法三（group by 分组，使用having 限定组员的个数）
SELECT
p.email AS Email
FROM
Person AS p
GROUP BY
p.email
HAVING
COUNT( p.email )> 1;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/69696.html
