leetcode 部门最高工资

/****************************
2019-2-11
部门最高工资

Employee
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

输出
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+
*****************************/


select a.Name as Department ,
	   b.Name as Employee,
	   c.Salary
from Department a
left join (
			select DepartmentID,max(Salary) as Salary
			from Employee
			group by DepartmentID
			) c
	on a.Id = c.DepartmentID
left join Employee b
	on a.Id = b.DepartmentID and c.Salary = b.Salary
where b.Name is not null
order by c.Salary asc