题目
最开始我的思路是
用group by分组 在max(Salary)但是这样查询 工资最高且工资相同的只能输出一个
然后换了个思路
首先根据部门分租,查询每个部门工资的最大值
select DepartmentId , max(Salary) from Employee f group by f.DepartmentId
然后 在关联两张表,根据 部门id和价格在这最大值里面 进行查询
然而 第一遍sql是这样写的
select
d.Name Department,
e.Name Employee,
Salary
from
Employee e
join
Department d on e.DepartmentId=d.Id
where
(e.DepartmentId,e.Salary) in
(select DepartmentId , max(Salary) from Employee group by e.DepartmentId)
这样查询 只能查询出一个部门的最高工资,经过各种查询,检查,过了半个小时,终于找到根源
就是查询最大价格的时候
select DepartmentId , max(Salary) from Employee
group by e.DepartmentId
这样查询只能查询到9000的那个价格
所以 经过修改后通过测试
select
d.Name Department,
e.Name Employee,
Salary
from
Employee e
join
Department d on e.DepartmentId=d.Id
where
(e.DepartmentId,e.Salary) in
(select DepartmentId , max(Salary) from Employee f group by f.DepartmentId)
写这篇博客 以祭奠此坑!!