查询部门最高工资的员工

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

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

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

编写一个 SQL 查询，找出每个部门工资最高的员工。

第一版sql：

select Department.name as Department,Employee.Name as Employee,max(Salary) as Salary from Employee 
left join Department on Department.Id = Employee.DepartmentId
group by DepartmentId

输出结果：

发现最高薪资查出来了，但是名字和薪资对不上
第二版sql:

select d.name as Department,e.name as Employee,Salary
from Employee e
inner join Department d 
on e.DepartmentId=d.id
where e.Salary>=(select max(Salary) as Salary from Employee where DepartmentId = d.id )

输出结果正确。

第三版：

先查出部门最高工资的思路

select d.name as department,e.name,e.salary from employee e
left JOIN (select id,max(salary) as salary,departmentid from employee GROUP by departmentid)c on e.DepartmentId=c.DepartmentId
left JOIN department d on e.departmentid= d.id
where e.departmentid = c.departmentid and e.salary = c.salary

执行结果

 

结果正确。

 

参考：https://blog.csdn.net/qq_43048011/article/details/82193952

　　   https://blog.csdn.net/qq_20309705/article/details/77865425

扫码关注我们

微信号：SRE实战

拒绝背锅 运筹帷幄