【数据库查询】184. Department Highest Salary

184. Department Highest Salary

Medium

SQL Schema

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 DepartmentId，然后MAX(Salary)，就完成了，但是运行结果却是错的！！

主要还是GROUP BY和MAX的问题，根据DepartmentId分组后，MAX作用于Salary列上，但是！无法确定与MAX(Salary)对应行的其他列的值！也就是说，用上面的做法，Salary输出正确，而Department和Employee两列不一定正确，所以不能这样做。

应该用子查询方式，子查询执行上面的操作，然后外面的SELECT查询| Department | Employee | Salary |这三列

同时注意不同的语言在实现上稍微不同，SQL SERVER里有WITH ... AS(SELECT ...)用法，建立临时表，在MYSQL里把子查询放在FROM子句里，也算是一个临时表吧，WHERE条件都一样

SQL SERVER：

WITH e AS (
    SELECT DepartmentId Did, MAX(Salary) s
    FROM Employee
    GROUP BY DepartmentId
)
SELECT Department.Name Department, Employee.Name Employee, e.s Salary
FROM Employee, Department, e
WHERE e.Did = Department.Id AND Employee.Salary = e.s
    AND Employee.DepartmentId = e.Did;

MYSQL：

SELECT Department.Name Department, Employee.Name Employee, Salary
FROM Employee, Department, (SELECT DepartmentId Did, MAX(Salary) s
    FROM Employee
    GROUP BY DepartmentId) AS e
WHERE e.Did = Department.Id AND Employee.Salary = e.s
    AND Employee.DepartmentId = e.Did;