Leetcode | Database | Department Highest Salary

• 题目描述
  给出下面employee和department表，写出一个sql查询语句找出每个部门中工资最高的员工。
  employee:
  
  department ：
  
  运行结果：
  
• 题目解答：
  第一步：先把employee表和department表连接起来

SELECT *
FROM Employee JOIN Department
ON Employees.DepartmentId=Departments.Id

第二步：在Employee表中，按部门分组，找出每个部门中工资较高的员工（之所以不直接运用这种方法是因为可能有同一个部门中工资同高的人存在，且employee表中也没有结果中要求的部门名称）

SELECT DepartmentId, MAX(Salary)
FROM Employee
GROUP BY DepartmentId

第三步:组合前两步得出最终结果：

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee JOIN Department 
ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )