题目如下:
输入输出
因为Employee表包含Salary和Department字段,我们可以以此在部门查询最高工资
select departmentId,max(salary) from Employee group by departmentId
然后,我们可以把表Employee和Department连接,在这时临时表力用in语句查询部门id和工资的关系
select d.name as Department,e.name as Employee,e.salary as 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 departmentId)
我一开始wher里面想写 (e.id,e.salary) in (select id,max(salary) from Employee group by departmentId),但是select id,max(salary) from Employee group by departmentId,查到的员工是每一部门最高的,如果有两个最高的话只会取一个,因为group by的原因。这样就不对的,需要使用departmentId,这样用部门id能查询每个部门每个人数据并附上最高工资过滤条件就能定位到准确的位置了。