题目:
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.
1、使用group和count
# Write your MySQL query statement below
SELECT Email FROM Person GROUP BY Email HAVING count(*) > 1;
2、使用自连接
# Write your MySQL query statement below
SELECT DISTINCT a.Email FROM Person a , Person b where a.Email = b.Email and a.Id != b.Id;
3、使用内连接
# Write your MySQL query statement below
SELECT DISTINCT a.Email FROM Person a JOIN Person b ON (a.Email = b.Email) WHERE a.Id <> b.Id
4、使用左连接
# Write your MySQL query statement below
SELECT DISTINCT a.Email FROM Person a
LEFT JOIN (SELECT Id, Email from Person GROUP BY Email) b
ON (a.email = b.email) AND (a.Id = b.Id)
WHERE b.Email IS NULL;