Department Highest Salary

表内自连接，分组排序
https://leetcode.com/problems/department-highest-salary/
方法一：
分组取最大值，https://blog.csdn.net/okiwilldoit/article/details/53099973

SELECT d.Name AS Department, e.Name AS Employee, t.Salary FROM  
Employee e   
INNER JOIN   
(SELECT DepartmentId, MAX(Salary) AS Salary FROM Employee GROUP BY DepartmentId) t  
USING(DepartmentId, Salary) ##等价于on e.DepartmentId = t.DepartmentId and e.Salary = t.Salary
INNER JOIN   
Department d  
ON d.id = t.DepartmentId

方法二：

SELECT D.Name AS Department,A.Employee,A.Salary FROM
(SELECT C.DepartmentId,C.Name AS Employee,C.Salary FROM
(SELECT E1.id,E1.Name,E1.Salary,E1.DepartmentId,COUNT(1) AS RANK FROM Employee E1
INNER JOIN 
(SELECT DISTINCT Salary,DepartmentId FROM Employee) E2
ON E1.DepartmentId=E2.DepartmentId AND E1.Salary<=E2.Salary
GROUP BY E1.id,E1.Name,E1.Salary,E1.DepartmentId) C WHERE C.RANK=1) A
INNER JOIN Department D ON D.Id=A.DepartmentId