一、题目

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

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

Department 表包含公司所有部门的信息。

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

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

二、解决思路

1.子查询在WHERE语句

（1）先对部门进行分组，求出每一个部门的最大薪资
（2）用 WHERE 语句过滤部门的最大薪资行
（3）使用JOIN ON 将两个表连接在一起

SELECT d.Name as Department,
e.Name as Employee,
e.salary as Salary

FROM Employee e JOIN Department d
ON e.DepartmentId=d.Id

# in之前和之后SELECT子句中SELECT的列数目应一致
WHERE (e.DepartmentId,e.salary) in
(SELECT e.DepartmentId,max(e.salary) as maxs
FROM Employee e
GROUP BY e.DepartmentId);

in之前和之后SELECT子句中SELECT的列数目应一致

WHERE (e.DepartmentId,e.salary) in
(SELECT e.DepartmentId,max(e.salary) as maxs
FROM Employee e
GROUP BY e.DepartmentId);

2 窗口函数

（1）使用rank() over(partition by d.Name order by e.Salary desc) as rank_先按照部门名分类，再按照薪资降序，当rank_=1时，就是该部门薪资最多的
（2）因为 WHERE 要使用rank_=1来过滤数据，所以需将SELECT语句放入FROM中

# Write your MySQL query statement below
SELECT Department,Employee,Salary
FROM

(SELECT 
d.Name as Department,
e.Name as Employee,
e.salary as Salary,
rank() over(partition by d.Name order by e.Salary desc) as rank_
FROM Employee e JOIN Department d
ON e.DepartmentId=d.id) as temp

WHERE rank_=1;