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.
找出重复的Email
方法一:select Email from Person group by Email having count(*) > 1
搭配group by .. having count(*) 找出每一组所含有行的数量
方法二:内连接或自连接,以Email相等,id不等为条件,最后获得结果去重
代码
select DISTINCT p1.Email from Person p1,Person p2 where
p1.Id <> p2.Id and p1.Email = p2.Email