题目地址：

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)