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.
My Solution:
SELECT DISTINCT
person1.Email
FROM
person person1
INNER JOIN person person2 ON person1.email = person2.email
WHERE
person1.id <> person2.id
思路:
先用inner join
拼接Person
表中含有相同Email的所有id
,然后再使用DISTINCT
关键字去除重复的记录。(性能较低,倒数12%)
Other People’s Solutions:
SELECT
Email
FROM
Person
GROUP BY
Email
HAVING
count(*) > 1
摘自 —— https://discuss.leetcode.com/topic/7485/i-have-this-simple-approach-anybody-has-some-other-way
作者:dashinghimay
OR
SELECT DISTINCT
Email
FROM
Person
HAVING
count(*) > 1
思路:
先查询出所有不重复的Email
,再使用count()
函数对结果进行筛选,最终筛选出次数大于一次的所有Email
。
注意:
(使用GROUP BY
可以限制查询结果中某一个字段不重复,而使用DISTINCT
则为限制查询结果中所有字段同时不重复)
例:
1.此处查询的是Id及Email字段,可以仅限制Email字段不重复。
SELECT Id, Email
FROM Person
GROUP BY Email DESC
2.此处查询的也是Id及Email字段,但限制为Id及Email字段同时不重复。
SELECT DISTINCT Id, Email
FROM Person
拓展:
HAVING 子句
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。SQL HAVING 子句 —— W3School
http://www.w3school.com.cn/sql/sql_having.asp