184. Department Highest Salary

问题描述

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+————+———-+——–+
| Department | Employee | Salary |
+————+———-+——–+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+————+———-+——–+

题目链接：

---

思路分析

有Employee表和Department表，找到不同部门中， 收入最高的人。

与选择每个部门中收入最高的三人类似，这个题目中也使用了subquery，在join两张表之后，再次query，找到e2中不同的department对应的最高的salary。

这里不能使用left join，因为会产生null值，所以需要全匹配。

代码

# Write your MySQL query statement below
select d.Name as Department, e1.Name as Employee, e1.Salary from
    Employee e1 join Department d on e1.DepartmentId = d.Id
    where e1.Salary = (select MAX(salary) from Employee e2 where e2.DepartmentId = d.Id)
    order by d.Name ASC

时间复杂度：未知
空间复杂度：未知

---

反思

有些复杂了，不过在实际的操作中不应当使用太多的subquery，这回大大的降低查询速度。