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

题意：

给定上述两个表，然后要求输出各个部门中的薪水最高的员工名字和薪水。

题解：

此题想到用表与表之间的连接来做。但是一开始我们可以先考虑两张表的内连接，将各部门的代号和部门中的最高薪水给找出来，用

select e.DepartmentId,max(e.Salary) as Salary,d.Name as Department from Employee e join Department d on e.DepartmentId = d.Id group by d.Name;

然后再考虑将上述语句得到的一张临时表和Employee表连接，得到相关联的部门中的薪水高的员工的名字。

select t.Department as Department,e.Name as Employee,t.Salary as Salary from Employee as e join (select e.DepartmentId,max(e.Salary) as Salary,d.Name as Department from Employee e join Department d on e.DepartmentId = d.Id group by d.Name) as t on t.DepartmentId = e.DepartmentId and t.Salary = e.Salary;