工资(Mysql多表查询)"/>
求部门最高工资(Mysql多表查询)
分享一下group by在以下的多表查询中不可用,先给出错误的SQL语句,后面再给出正确语句和分析.
员工表employee,包括Id,Name,Salary,和DepartmentID,DepartmentID与部门id对应
部门表Department
需求 : 编写SQL语句查询每个部门中薪资最高的员工。每个部门只显示最高的工资的员工信息
* 笔者看到需求第一时间想到分组先求各个部门工资,再查询员工*
1.求各个部门最高工资
select max(salary)
from employee
group by DepartmentId
2.将最高工资表作虚拟表,查询出工资与最高工资相同的员工
select Department.name Department, employee.name Employee ,salary
from employee join department
on employee.DepartmentId = department.id
where salary in (select max(salary)from employeegroup by DepartmentId)
貌似得到了想要的结果,但是出现以下情况时,得到的结果就是错误的:
- 当我改动joe的工资为80000时,得到的结果出乎意料:
为什么会出现这种情况?
where salary in (select max(salary)from employeegroup by DepartmentId)
- 这里筛选条件相当是salary in (80000,90000)
下面给出正确的方式
selectd.name Department,e1.name Employee,e1.salary Salary
from employee e1 left join employee e2
on e1.salary < e2.salary and e1.DepartmentId=e2.DepartmentId
join Department d
on e1.DepartmentId = d.id
where e2.id is null
- 一段一段看
select*
from employee e1 left join employee e2
on e1.salary < e2.salary and e1.DepartmentId=e2.DepartmentId
自连接,连接的条件是e1工资比e2工资低,部门号相同,这是当同部门中e1中没有比自己工资高的,自动补null,也就是上图中的情况
此时连接部门表,再筛选出e2的id是null的行就是所要求的
本文例题来自leetcode,也是笔者在刷题时踩到的坑,下面给出建表的SQL的语句
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));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');insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');
更多推荐
求部门最高工资(Mysql多表查询)
发布评论