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

难度：中等

表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  Department,  as Employee,  as Salary
FROM Employee E1 join Department D
WHERE  =  and  >= (SELECT MAX(Salary) from Employee E2 WHERE  = );

• 答案二：

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

来源：http:///blog/