184. Department Highest Salary

184. Department Highest Salary

Question

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

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

The Department table holds all departments of the company.

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

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

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

二刷

1. 使用join将两张表连接起来。使用聚合函数筛选出最大值，最后通过in来选取。
   • 思考：从哪张表连接向哪张表？我们要选出部门中的最大工资，所以使用部门最为左表。

     # Write your MySQL query statement below
     SELECT d.Name AS Department,
     e.Name as Employee,
     e.Salary as Salary
     FROM Department d #通过外连接将两张表合并
     LEFT JOIN Employee e
     ON d.Id = e.DepartmentId
     WHERE (d.Id, e.Salary) IN ( #通过IN判断出应该显示哪些字段
     SELECT e.DepartmentId, max(e.Salary) from Employee e GROUP BY e.DepartmentId    #使用聚合函数筛选出最大值
     );
     

引用

1.leetcode-184-Department Highest Salary 优化记录