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

方法：采用JOIN和IN语句[Accepted]

Algorithm

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

SELECT
    DepartmentId, MAX(Salary)
FROM
    Employee
GROUP BY DepartmentId;

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

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

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

MySQL

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
    )
;