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.
Have you met this question in a real interview?
Yes
No
此题是leetcode
Duplicate Emails 想着一晚上怎么变成一个逼格高点的人 就打开leetcode 就当玩儿吧 。。坚持
解法:本题我认为用distinct 就好了,因为只需要查询出email列就可以了
所以代码如下
select distinct email from Person
所以代码如下
select distinct email from Person
对distinct的用法我在网上查到,例如表table表
字段1 字段2
id name
1 a
2 b
3 c
4 c
5 b
字段1 字段2
id name
1 a
2 b
3 c
4 c
5 b
使用distinct 可以做到返回一列 比如select distinct name from table
但是如果我写成 select distinct name,id from table 返回的结果是
id name
1 a
2 b
3 c
4 c
5 b
1 a
2 b
3 c
4 c
5 b
因为distinct 是作用于两列的 所以要有两列的内容重复才会返回正确的值出来,当然如果写成 select id,distinct name from table 会报错。
所以解决办法是 select *,count(distinct name)from table group by name
对group by的用法 就是group by 加列名 ,还有group by 必须放在order by 和limit前。
mysql limit 用法limit是---------mysql的语法select * from table limit m,n其中m是指记录开始的index,从0开始,表示第一条记录n是指从第m+1条开始,取n条。select * from tablename limit 2,4即取出第3条至第6条,4条记录
key:
# Write your MySQL query statement below
SELECT DISTINCT a.Email
FROM Person a JOIN Person b
ON(a.Email=b.Email and a.Id<>b.Id)
SELECT DISTINCT a.Email
FROM Person a JOIN Person b
ON(a.Email=b.Email and a.Id<>b.Id)
解法一
解法二
SELECT Email FROM Person GROUP BY Email HAVING COUNT(*) > 1