题目描述：
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保存了雇员的Id，姓名，薪水以及部门Id。
部门表Department保存了部门的Id和名称。
编写一个SQL查询，找出每一个部门中薪水最高的员工信息。样例及结果如上所示。
解题思路：
首先使用临时表t查询出每一个部门的最高薪水，然后使用薪水值和部门Id与雇员表Employee做内连接，再通过部门Id与部门表Department做内连接。
SQL语句：
# Write your MySQL query statement below
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)
INNER JOIN
Department d
ON d.id = t.DepartmentId
本文链接：http://bookshadow.com/weblog/2015/01/21/leetcode-department-highest-salary/
请尊重作者的劳动成果，转载请注明出处！书影博客保留对文章的所有权利。
捐赠书影博客
