【Leetcode】184. Department Highest Salary

题目地址：

https://leetcode.com/problems/department-highest-salary/

给定两个表，分别是Employee表和Department表，例子如下：

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

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

要求返回每个部门工资最高的人的部门名，姓名和工资。

可以将Employee表先按DepartmentId分组然后SELECT出部门id和最大工资作为子查询，然后再两个表JOIN起来查询出需要的信息。代码如下：

SELECT Department.name `Department`, Employee.name `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)