MySQL查询所有员工的邮箱_mysql数据库查询练习二-【比经理工资还高员工姓名】【查找重复的邮箱】...

1.查询工资比他经理的工资还高的员工的姓名

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+

| Id | Name | Salary | ManagerId |

+----+-------+--------+-----------+

| 1 | Joe | 70000 | 3 |

| 2 | Henry | 80000 | 4 |

| 3 | Sam | 60000 | NULL |

| 4 | Max | 90000 | NULL |

+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+

| Employee |

+----------+

| Joe |

+----------+

s1表： s2表：

+----+-------+--------+-----------+ +----+-------+--------+-----------+

| Id | Name | Salary | ManagerId | | Id | Name | Salary | ManagerId |

+----+-------+--------+-----------+ +----+-------+--------+-----------+

| 1 | Joe | 70000 | 3 | | 1 | Joe | 70000 | 3 |

| 2 | Henry | 80000 | 4 | | 2 | Henry | 80000 | 4 |

| 3 | Sam | 60000 | NULL | | 3 | Sam | 60000 | NULL |

| 4 | Max | 90000 | NULL | | 4 | Max | 90000 | NULL |

+----+-------+--------+-----------+ +----+-------+--------+-----------+

2.解答

(1)方法一

select s1.Name as Employee from Employee as s1,Employee as s2 where s1.ManagerId=s2.Id and s1.Salary > s2.Salary

(2)方法二

Select s1.Name as Employee from Employee s1 join Employee s2 on s1.ManagerId = s2.Id and s1.Salary > s2.Salary

或

Select s1.Name as Employee from Employee s1 join Employee s2 on s1.ManagerId = s2.Id where s1.Salary > s2.Salary

(3)方法三:这种方法涉及到子查询，所以查询效率低

select s1.Name as Employee from Employee as s1 where s1.Salary > (select s2.Salary from Employee as s2 where s1.ManagerId = s2.Id);

3.编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+

| Email |

+---------+

| a@b.com |

+---------+

说明：所有电子邮箱都是小写字母。

4.解答

(1)方法一：

select distinct p1.Email from Person as p1,Person as p2 where p1.Email=p2.Email and p1.Id !=p2.Id;

(2)方法二：

select Email

from Person

group by Email

having count(*) > 1;