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

题目大意:

雇员表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/
请尊重作者的劳动成果,转载请注明出处!书影博客保留对文章的所有权利。

如果您喜欢这篇博文,欢迎您捐赠书影博客: ,查看支付宝二维码

Pingbacks已关闭。

暂无评论

张贴您的评论