181-185 数据库专题二
181.超过经理收入的员工
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 |
两张表，一张存员工信息，一张存经理信息
SELECT e.Name AS Employee -- 建表头
FROM Employee e INNER JOIN Employee m -- 员工表-经理表
ON e.ManagerID = m.Id -- 员工的经理ID = 经理表的ID
AND e.Salary > m.Salary -- 员工工资 > 经理工资
182.查找重复的电子邮箱
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：
| Id | |
|---|---|
| 1 | a @b.com |
| 2 | c @d.com |
| 3 | a @b.com |
根据以上输入，你的查询应返回以下结果：
| a @b.com |
说明：所有电子邮箱都是小写字母。
仍然使用两个表，一个基本表，一个对照表。
SELECT DISTINCT base.Email AS Email --建表头
FROM Person base, Person chec -- 两个表互相对照
WHERE base.Email = chec.Email -- 筛选出Email相同
AND base.ID <> chec.ID -- 且ID不同的
183.从不订购的客户
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
| Id | Name |
|---|---|
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
Orders 表：
| Id | CustomerId |
|---|---|
| 1 | 3 |
| 2 | 1 |
例如给定上述表格，你的查询应返回：
| Customers |
|---|
| Henry |
| Max |
一开始我的想法是，先找到所有的有购买的用户，然后剔除。但是实现的时候我是储存了有购买的用户的Name，然后相同的Name剔除。这种做法导致同名的客户不再输出
改成用ID剔除就可以了。
SELECT c.Name AS Customers -- 建表头
FROM Customers c -- 来源表
WHERE c.Id NOT IN( -- 外层筛选
SELECT c1.Id -- 提取内层筛选的Id
FROM Customers c1, Orders o -- 来源表
WHERE c1.Id = o.CustomerId -- 内层筛选，找出有购买的
)
他人的博客中使用了LEFT JOIN的方法，最终对应的CustomerId为空的用户，即为没有购买的用户
SELECT c.Name AS Customers -- 建表头
FROM Customers c -- 来源表
LEFT JOIN Orders o -- 左连接，以所有Customer做依据
ON c.Id = o.CustomerId -- 关联依据
WHERE o.CustomerId is null -- 筛选
184.部门工资最高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
| Id | Name | Salary | DepartmentId |
|---|---|---|---|
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
Department 表包含公司所有部门的信息。
| Id | Name |
|---|---|
| 1 | IT |
| 2 | Sales |
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
| Department | Employee | Salary |
|---|---|---|
| IT | Max | 90000 |
| Sales | Henry | 80000 |
首先找到每个部门的最高薪水，然后找到对应该部门中，拿到这个薪水的人，建表
SELECT d.Name AS Department, -- 建表头
e.Name AS Employee,
e.Salary AS Salary
FROM Employee e -- 来源表
INNER JOIN Department d -- 找到所有员工-部门对应关系
ON e.DepartmentId = d.Id
WHERE e.Salary >= ( -- 筛选出来的Salary需要满足条件
SELECT MAX(e1.Salary) FROM Employee e1 -- 找到对应外部匹配部门的，工资最大值
WHERE e.DepartmentId = e1.DepartmentId
)
185.部门工资前三高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
| Id | Name | Salary | DepartmentId |
|---|---|---|---|
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
Department 表包含公司所有部门的信息。
| Id | Name |
|---|---|
| 1 | IT |
| 2 | Sales |
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：
| Department | Employee | Salary |
|---|---|---|
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
184题的延伸。从找到部门最大工资，变为找到第3多工资，然后再筛选出所有比这个工资高的员工
SELECT d.Name AS Department, -- 建表头
e.Name AS Employee,
e.Salary AS Salary
FROM Employee e -- 来源表
INNER JOIN Department d -- 连接两表
ON e.DepartmentId = d.Id -- 建立映射关系
WHERE e.Salary >= IFNULL(( -- 判断是否为空，如果为空则记为0
SELECT DISTINCT e1.Salary -- 以工资做排序依据，而不是人
FROM Employee e1
WHERE e1.DepartmentId = e.DepartmentId
ORDER BY e1.Salary DESC -- 对该部门工资排序
LIMIT 2, 1 -- 选出第3大的值
), 0)
ORDER BY d.Id ASC, -- 先按部门Id顺序排序
e.Salary DESC -- 同部门情况下按工资高低逆序
