50 - 查找重复的电子邮箱

题目

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (Id, Email) values ('1', '[e-mail]')
insert into Person (Id, Email) values ('2', '[e-mail]')
insert into Person (Id, Email) values ('3', '[e-mail]')

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

示例：

+----+---------+ | Id | Email | +----+---------+ | 1 | [e-mail] | | 2 | [e-mail] | | 3 | [e-mail] | +----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+ | Email | +---------+ | [e-mail] | +---------+

说明：所有电子邮箱都是小写字母。

解答

之前拼两个表的做法失效了，因为只出现一次放在两个表里面，就是出现两次了。。

作者：LeetCode 链接：https://leetcode-cn.com/problems/two-sum/solution/cha-zhao-zhong-fu-de-dian-zi-you-xiang-by-leetcode/

select Email from (select email, count(email) as num from person group by email) as whatever where num >1

Runtime: 186 ms, faster than 83.25% of MySQL online submissions forDuplicate Emails.

Memory Usage: N/A

• Group by email，意思是将挑选的结果展示，email相同，对应值加在一起。
• 在搜索结果里面搜索(sub-query)，第一个搜索表一定要起一个别名，所以要加一个as，估计是为了方便以后用这个临时表。

select email from person group by email having count(email) > 1

Runtime: 203 ms, faster than 56.22% of MySQL online submissions forDuplicate Emails.

Memory Usage: N/A

• group by的筛选条件是having

  select is where

  join is on

为啥不统一一下？？？

• 感觉用having比差临时表要低效呢。