Leetcode 184. Department Highest Salary

184. Department Highest Salary

原题目链接:184. Department Highest Salary

Employee表有所有员工的信息。每一位员工有一个Id,salary,也有一列表示部门Id(DepartmentId)。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department表包含公司的所有的部门。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

写一条SQL查询语句找出每一个部门当中薪酬最高的员工。对于上面的表格,在IT部门当中Max的薪酬最高,Sales部门当中Henry的薪酬最高。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Solution

方法:采用JOININ语句[Accepted]

Algorithm

考虑到Employee表包含SalaryDepartmentId信息,我们查询一个部门当中的最小薪资。

1
2
3
4
5
SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId;

注意:有可能多个员工拥有同样高的薪资,因此,在这个查询当中不要包含员工姓名信息是安全的。

| DepartmentId | MAX(Salary) |
|--------------|-------------|
| 1            | 90000       |
| 2            | 80000       |

然后我们可以将Employee表和Department表连接到一起,然后查询用IN表达式在临时表当中查询(DepartmentId,Salary)。

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;