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.
解决方法:
我是通过看成两个表(内交),内交的id有相同的邮箱说明重复使用了。
/*把一个表看成a,b两个表*/
select distinct(a.Email) from Person a,Person b where a.Id != b.Id and a.Email=b.Email;
别人的sq通过group by分组,要是有邮箱的数目大于1说明重复:
select email
from person
group by email
having count(email) >1;
这个执行的效率高,时间短。