Leetcode: Department Highest Salary

最新推荐文章于 2019-04-08 14:23:06 发布

原创最新推荐文章于 2019-04-08 14:23:06 发布 · 479 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

leetcode 专栏收录该内容

216 篇文章

订阅专栏

本文介绍了一种使用SQL查询的方法，来找出每个部门中薪资最高的员工。通过联接Employee和Department表，并使用分组和最大值函数实现目标。示例展示了如何从给定的数据集中获取所需结果。

Question

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 |
+————+———-+——–+

Other′s Solution

# Write your MySQL query statement below
select d2.Name, e2.Name, temp.maxs
from 
(select max(e.Salary) as maxs, d.id as did
From Employee e, Department d
where e.DepartmentId=d.Id
group by d.Id) as temp, Employee e2, Department d2
where  temp.did=e2.DepartmentId and temp.maxs=e2.Salary
    and temp.did=d2.Id