LeetCode184-MySQL-部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| 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 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

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

1、链接两个表，将部门Id对应到部门名字

2、条件：每个部门下最大的薪水

So show you my code:

# Write your MySQL query statement below
SELECT Department.Name AS Department, Employee.Name AS Employee, Employee.Salary AS Salary
FROM Employee
JOIN Department
ON Employee.DepartmentId = Department.Id
WHERE   (Employee.DepartmentId, Employee.Salary) IN 
        (
        SELECT DepartmentId, MAX(Salary) AS Salary
        FROM Employee
        GROUP BY DepartmentId
        )

Runtime: 277 ms

注意：不能直接在SELECT中取MAX(Salary)然后GROUP BY Department.Name

因为一来在HIVE语法中，不能仅仅只GROUP BY Department.Name, 同时GROUP BY中还要加上Employee.Name。

尽管对于MySQL而言可以不用GROUP BY Employee.Name,但是实际上输出结果中并不能将雇员名字与所在部门名字对应（跑一下就知道了）

因此，选择在条件语句中子查询的方式进行条件限制