【LeetCode】184.部门最高工资

184.部门最高工资

Employee表有所有员工。每个员工有 Id,salary 和 department Id 信息。

用到的表和数据SQL：

Create table If Not Exists Employee (Idint, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Idint, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary,DepartmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (Id, Name, Salary,DepartmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('4', 'Max', '90000', '1');
Truncate table Department;
insert into Department (Id, Name) values('1', 'IT');
insert into Department (Id, Name) values('2', 'Sales');

答案：

首先让两个表内连接，然后将结果表需要的列都标明，然后就是要找最高的薪水，我们用Max关键字来实现。

select d.Name Department ,e1.Name Employee,e1.Salary Salary 
from Employee e1
inner join Department d on e1.DepartmentId= d.Id
wheree1.Salary in (select Max(e2.Salary) from Employee e2 where e2.DepartmentId =e1.DepartmentId)

有些童鞋觉得我上面这个答案理解起来比较费劲，然后我就写了下面这个答案，理解起来应该还是比较简单的，而且执行效率更高一些。

select d.Name as Department,e1.Name as Employee,e1.Salary as Salary
from Employee e1 , Department d
where e1.DepartmentId=d.Id  and (e1.Salary,e1.DepartmentId) 
in (select max(Salary),DepartmentId from Employee group by DepartmentId) 

写的比较粗糙，有不理解的可以扫描二维码加QQ群找我解答。