[LeetCode#182]Duplicate Emails
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.
本题是找表Person中重复的邮箱,并且该表中的邮箱字母都是小写字母。这道题比较简单,主要涉及到的知识点就是group by 与 having count 的组合,具体的解法如下:
解法一:
SELECT Email from person GROUP BY Email HAVING COUNT(Email)>1;
解法二:
我们可以使用内交来做,用Email来内交两个表,然后返回Id不同的行,则说明两个不同的人使用了相同的邮箱:
SELECT DISTINCT p1.Email FROM person p1 JOIN person p2 on p1.Email = p2.Email WHERE p1.Id <> p2.Id;
作者:薛定谔的ハチ公
申明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。