LeetCode：182. Duplicate Emails

最新推荐文章于 2023-12-05 23:06:10 发布

原创最新推荐文章于 2023-12-05 23:06:10 发布 · 265 阅读

0 ·

CC 4.0 BY-SA版权

Python 同时被 2 个专栏收录

36 篇文章

订阅专栏

LeetCode

20 篇文章

订阅专栏

本文介绍了一个SQL查询案例，通过使用GROUP BY与HAVING COUNT(*) > 1来找出表Person中所有重复的电子邮件地址。

题目：
Write a SQL query to find all duplicate emails in a table named Person.

+—-+———+
| Id | Email |
+—-+———+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+—-+———+
For example, your query should return the following for the above table:

+———+
| Email |
+———+
| a@b.com |
+———+
Note: All emails are in lowercase.

Answer：
思路：GROUP BY和HAVING COUNT用法。

SELECT Email FROM person GROUP BY Email HAVING COUNT(Email) > 1

附表email的sql：

Create table If Not Exists Person (Id int, Email varchar(255));
Truncate table Person;
insert into Person (Id, Email) values ('1', 'a@b.com');
insert into Person (Id, Email) values ('2', 'c@d.com');
insert into Person (Id, Email) values ('3', 'a@b.com');