leetcode with MySQL:182. Duplicate Emails

题目:

Table: Person

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write an SQL query to report all the duplicate emails.

Return the result table in any order.

给定一个table,找到重複出现的Email

SELECT DISTINCT a.EmailFROM Person AS a, Person AS bWHERE a.Email = b.Email and a.id <> b.id;

将该表(a表)跟另一个自己(b表)比较
条件设两表Email相同但id不同者
然后在其中不重複的选出Email(DISTINCT)
最后执行时间311ms(faster than 88.85%)

另外我在讨论区有看到用GROUP BY的解法
起初这语法我并不知道
但在了解过后大概知道它的功用了

SELECT EmailFROM PersonGROUP BY EmailHAVING COUNT(Email) > 1;

透过GROUP BY将资料依Email分类
再选出其中Email出现次数(COUNT)超过1的
最后执行时间278ms(faster than 99.31%)

那我们下题见