数据库：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  |
+------------+----------+--------+

---

非常常见的数据库查询问题，以部门为单位求最高工资。

上传这道题的目的是为提高对查询语句的理解：

先公布解法：

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id WHERE Salary =
(SELECT MAX(Salary) FROM Employee e2 WHERE e1.DepartmentId = e2.DepartmentId);

对网上这种解法一开始并不能看懂，因为固定思维on后面有一个=连接的条件，总是会让我想到结果集只有一行数据。然而并不是如此，首先，select语句可以看成是循环语句，遍历表中的每一行数据并筛选出符合条件的，如果这样理解，那么=号后面子查询的结果会随遍历而变化，当指针指到并列后表中的第一条数据时，子查询结果为90000，（因为此时DepartmentId为1）.因此第一条数据不符合条件。以此类推能够得到正确结果。

我的第一次解法：

SELECT d.Name AS Department, e1.Name AS Employee, MAX(e1.Salary) Salary FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id GROUP BY Department;

看似没有什么问题，按部门为单位求最大值，因为之前按部门为单位求和时就是这样写的。两者的区别为：按部门求和问题最终数据是所属于部门的，而最大值是所属于部门中的某人的，这样执行的结果就是工资和员工不匹配，因为MAX只负责求某列最大值，这样执行的结果是最大值正确无误而对应的员工则是部门中编号靠前的第一个员工，因为并没有条件限制员工。剩下的被舍弃，因为MAX只有一行数据。

总结：

select语句的正确理解和group by 的适当运用。