Leetcode 184. Department’s highest salary

184. Highest departmental salary

Answer key:

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)
Copy the code

Analysis:

Query the maximum value of DepartmentId field GROUP first, and get the maximum value under different DepartmentId

SELECT DepartmentId, max( Salary ) 
	FROM Employee 
GROUP BY DepartmentId 
Copy the code

Join the Department table according to DepartmentId field. Select department. Name from Salary and DepartmentId

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)
Copy the code