【sql学习】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.

自己的代码：

Runtime: 182 ms, faster than 82.50% of MySQL online submissions for Duplicate Emails.

SELECT
	Email
FROM
	Person
GROUP BY
	Email
HAVING
	COUNT(Email) > 1

SQL分组：GroupBy+Having

• group by + having： 用来分组查询后，再指定一些条件来输出查询结果
• having 和 where 一样，但 having 只能用于 group by

having 和 where 的区别：

• having 是在分组后对数据进行过滤，where 是在分组前对数据进行过滤
• having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数
• where 是对分组前记录的条件，如果某行记录没有满足where字句的条件，那么这行记录不会参加分组；而having是对分组后数据的约束

​SQL分组+聚合函数

sum;avg;max;min;

select deparmant, GROUP_CONCAT(salary), SUM(salary),AVG(salary) 平均工资,MAX(salary) 最高工资 from employee GROUP BY deparmant;
-- 根据department分组，计算各部门下工资总数，平均工资，最高工资!

 

方法二：

Runtime: 237 ms, faster than 8.41% of MySQL online submissions for Duplicate Emails.

SELECT DISTINCT
	p1.Email
FROM
	Person p1
INNER JOIN Person p2 ON p1.Email = p2.Email
WHERE
	p1.Id <> p2.Id;

mysql> select * from Person;
+------+---------+
| Id   | Email   |
+------+---------+
|    1 | a@b.com |
|    2 | c@d.com |
|    3 | a@b.com |
+------+---------+
3 rows in set (0.00 sec)

mysql> select * from Person p1 inner join Person p2 on p1.Email=p2.Email; -- 内连接
+------+---------+------+---------+
| Id   | Email   | Id   | Email   |
+------+---------+------+---------+
|    1 | a@b.com |    1 | a@b.com |
|    3 | a@b.com |    1 | a@b.com |
|    2 | c@d.com |    2 | c@d.com |
|    1 | a@b.com |    3 | a@b.com |
|    3 | a@b.com |    3 | a@b.com |
+------+---------+------+---------+
5 rows in set (0.00 sec)

mysql> select * from Person p1 left join Person p2 on p1.Email=p2.Email; -- 左连接
+------+---------+------+---------+
| Id   | Email   | Id   | Email   |
+------+---------+------+---------+
|    1 | a@b.com |    1 | a@b.com |
|    1 | a@b.com |    3 | a@b.com |
|    2 | c@d.com |    2 | c@d.com |
|    3 | a@b.com |    1 | a@b.com |
|    3 | a@b.com |    3 | a@b.com |
+------+---------+------+---------+
5 rows in set (0.00 sec)

mysql> select * from Person p1 right join Person p2 on p1.Email=p2.Email; -- 右连接
+------+---------+------+---------+
| Id   | Email   | Id   | Email   |
+------+---------+------+---------+
|    1 | a@b.com |    1 | a@b.com |
|    3 | a@b.com |    1 | a@b.com |
|    2 | c@d.com |    2 | c@d.com |
|    1 | a@b.com |    3 | a@b.com |
|    3 | a@b.com |    3 | a@b.com |
+------+---------+------+---------+
5 rows in set (0.00 sec)

mysql>

 

1）主要掌握左右连接，内连接

2）掌握不等于：<>

3）掌握distinct用法