博客

Employee表：

Department表：

编写一个SQL语句，找出每个部门薪资最高的员工。

应得查询结果如下：

#分解步骤：
#找到每个部门薪资最高的信息（表a)
select max(Salary) ms,DepartmentId from Employee group by DepartmentId
#找到每个部门最高薪资对应的员工信息(表b)
select e.Name,e.Salary,e.DepartmentId from Employee e,a where e.Salary=a.ms and e.DepartmentId=a.DepartmentId
#找到对应的部门名称
select d.name Department,b.name Employee,Salary from  b , Department d where b.DepartmentId =d.id;
#整合
select d.name Department,b.name Employee,Salary from (
    select e.Name,e.Salary,e.DepartmentId from Employee e,
    (select max(Salary) m,DepartmentId from Employee group by DepartmentId) a
    where e.Salary =a.m and e.DepartmentId =a.DepartmentId) b , Department d where
b.DepartmentId =d.id;