題目:

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.Email
FROM Person AS a, Person AS b
WHERE a.Email = b.Email and a.id <> b.id;

將該表(a表)跟另一個自己(b表)比較
條件設兩表Email相同但id不同者
然後在其中不重複的選出Email(DISTINCT)
最後執行時間311ms(faster than 88.85%)

另外我在討論區有看到用GROUP BY的解法
起初這語法我並不知道
但在了解過後大概知道它的功用了

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

透過GROUP BY將資料依Email分類
再選出其中Email出現次數(COUNT)超過1的
最後執行時間278ms(faster than 99.31%)

那我們下題見