184. 部门工资最高的员工

表： Employee

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。

表： Department

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。

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

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

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

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

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

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

drop table if EXISTS Employee;
Create table If Not Exists Employee (id int, name varchar(20),salary int, departmentId int);

drop table if EXISTS Department;
Create table If Not Exists Department (id int, name varchar(20));

insert into Employee values (1,'Joe',70000,1);
insert into Employee values (2,'Henry',80000,2);
insert into Employee values (3,'Sam',60000,2);
insert into Employee values (4,'Max',90000,1);
select * from Employee;

insert into Department values (1,'IT');
insert into Department values (2,'Sales');
select * from Department;

select Department,Employee,Salary
from (
select d.Name  Department,e.Name Employee, e.Salary,
rank() over(partition by d.id order by Salary desc) rk // 这句话在这里就是筛选条件
from Employee e join Department d
on e.DepartmentId=d.id
)tmp
where rk = 1

使用开窗函数解决了group by无法取出聚合前数据的问题
分组排序取出第一条