【leetcode Database】184. Department Highest Salary

题目：

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

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

The Department table holds all departments of the company.

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

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解析：

看到这道题目，我首先想到的是用group by对部门进行分组，然后再select每个部门中工资最高的人。最初代码如下

# Write your MySQL query statement below
SELECT d.Name AS Department, e.Name AS Employee,MAX(e.Salary) AS Salary FROM Department AS d 
INNER JOIN Employee AS e ON d.Id = e.DepartmentId GROUP BY e.DepartmentId;

但是，提交后发现是错误的。这是因为少考虑了一种情况，每个部门的最高工资可能不止一个人！正确的做法应该是把检索出来的结果作为一个数据表，然后再把员工表中与最高工资相等且是该部门的员工检索出来。修正后的代码如下：

# Write your MySQL query statement below
SELECT t.Department, p.Name AS Employee, t.Salary FROM Employee AS p,
(SELECT d.Id,d.Name AS Department,MAX(e.Salary) AS Salary FROM Department AS d 
INNER JOIN Employee AS e ON d.Id = e.DepartmentId GROUP BY e.DepartmentId) AS t 
WHERE p.Salary = t.Salary AND p.DepartmentId = t.Id;