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，求各个部分中工资最高的。

思路：先根据DepartmentID分组计算出最大值,得到临时表，再与Employee表内连接，再与Deparment表内连接

SELECT d.Name AS Department, e.Name AS Employee, t.Salary FROM
Employee e 
INNER JOIN 
(SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) t
USING(DepartmentId, Salary) #等价于e.DepartmentId = t.DepartmentId and e.Salary = t.Salary
INNER JOIN 
Department d
ON d.id = t.DepartmentId