50 - 查找重复的电子邮箱

题目

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')

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

示例：

+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+

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

+---------+ | Email | +---------+ | a@b.com | +---------+

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

解答

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

作者：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相同，对应值加在一起。 image-20190814095324272

  • 在搜索结果里面搜索(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比差临时表要低效呢。

Previous4-3sumNext176 - 621 任务调度器

Last updated

Was this helpful?