184.部门最高工资

leetcode 184.部门最高工资_leetcode

题解：

SELECT T1.name department, T.name employee, T.salary
  FROM employee T, department T1
 WHERE T.departmentid = T1.id
   AND (salary, departmentId) IN
       (SELECT MAX(salary), departmentId FROM employee GROUP BY departmentId)

分析：

GROUP BY 字段名 # 根据字段进行分组
先对 DepartmentId 字段分组查询最大值，得到不同 DepartmentId 下的最大值

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

再根据 DepartmentId 字段连接 Department 表，根据 Salary 和 DepartmentId 查找 Department.Name 字段

SELECT T1.name department, T.name employee, T.salary
  FROM employee T, department T1
 WHERE T.departmentid = T1.id
   AND (salary, departmentId) IN
       (SELECT MAX(salary), departmentId FROM employee GROUP BY departmentId)