面试题：查询部门最高工资的员工信息

难度：中等

表 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    |
+----+----------+

写一段SQL查找出各部门工资最高的员工信息。如上所示，IT部门工资最高的是Max，Sales部门工资最高的是Henry。

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

答案一：

SELECT D.Name as Department, E1.Name as Employee, E1.Salary as Salary
FROM Employee E1 join Department D
WHERE E1.DepartmentId = D.Id and E1.Salary >= (SELECT MAX(Salary) from Employee E2 WHERE E1.DepartmentId = E2.DepartmentId);

 

答案二：

SELECT b.Name as Department, a.name as Employee, a.Salary as Salary
FROM Employee a, Department b, (select max(Salary) as Salary, DepartmentId from Employee group by DepartmentId) c
WHERE a.DepartmentId = b.Id and a.Salary = c.Salary and a.DepartmentId = c.DepartmentId