LeetCode数据库题目Oracle解答：181. 超过经理收入的员工、182. 查找重复的电子邮箱、 183. 从不订购的客户、184. 部门工资最高的员工

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 e1.Name AS Employee
FROM Employee e1
WHERE EXISTS (
		SELECT 1
		FROM Employee e2
		WHERE e2.id = e1.ManagerId
			AND e2.Salary < e1.Salary
		)

SELECT e.Name as Employee
FROM Employee e
WHERE Salary > (
		SELECT Salary
		FROM Employee
		WHERE Id = e.Managerid
		)

182. 查找重复的电子邮箱

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

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

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

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

答案：

SELECT p.Email
FROM Person p
GROUP BY p.Email
HAVING count(id) > 1

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       |
+-----------+

答案：

SELECT c.Name AS Customers
FROM Customers c
WHERE NOT EXISTS (
		SELECT 1
		FROM Orders o
		WHERE o.CustomerId = c.id
		)

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  |
+------------+----------+--------+

解题思路：

1.子查询求出每个部门的最高Salary

2.再与Employee，Department关联得出结果。

答案：

SELECT d.Name Department
	,e1.Name Employee
	,e1.Salary
FROM Employee e1
	,Department d
	,(
		SELECT max(Salary) Salary
			,DepartmentId
		FROM Employee
		GROUP BY DepartmentId
		) e2  --1.子查询求出每个部门的最高Salary
WHERE e1.Salary = e2.Salary
	AND e1.DepartmentId = e2.DepartmentId
	AND e1.DepartmentId = d.Id