部门工资最高的员工.png
求最高工资的员工，就是求在相同部门的情况下，不存在比这个人的工资高的员工的情况，下面用了关联子查询
SELECT d.name Department, e.Name Employee, e.Salary
FROM Employee e JOIN Department d ON e.DepartmentId = d.Id
WHERE NOT EXISTS (SELECT * FROM Employee e2 WHERE e2.DepartmentId = e.DepartmentId AND e2.Salary > e.Salary)
#在部门id相同的情况下，不存在薪水比某人更高 （NOT EXISTS）
也可以换一种思路，先求出部门和对应的最高工资作为中间表，然后在和员工表进行join,如下所示
SELECT Department,Employee.Name Employee, C.Salary FROM Employee JOIN
(SELECT d.name Department,d.id , MAX(e.Salary) Salary
FROM Employee e JOIN Department d ON e.DepartmentId = d.Id
GROUP BY d.id) C ON C.Salary = Employee.Salary AND C.id = Employee.DepartmentId
