题目地址:
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)