题目:
表Employee
有所有员工的信息。每位员工都有一个Id,薪水和其所属部门的Id。
Id | Name | Salary | DepartmentId |
---|---|---|---|
1 | Joe | 70000 | 1 |
2 | Henry | 80000 | 2 |
3 | Sam | 60000 | 2 |
4 | Max | 90000 | 1 |
表 Department
存储了所有部门的名称和对应的Id。
Id | Name |
---|---|
1 | IT |
2 | Sales |
找出每个部门薪水最高的员工。
结果如下:
Department | Employee | Salary |
---|---|---|
IT | Max | 90000 |
Sales | Henry | 80000 |
解析:
题目涉及两张表,并且有分组(按部门将员工分组)操作,因此可能会使用到 group by
和join
关键字。
-
第一步:
我们先写出题目要求的查询结果的属性框架,如下:
select
Department.Name as Department,
Employee.Name as Employee,
Employee.Salary as Salary
from
Employee
join
Department on Department.Id=Employee.DepartmentId
where
...//未完待续
此时我们对数据没有做任何检索,数据的输出则是所有员工的数据。
第二步:
where后的分组操作。先出每个部门最高薪水和部门的Id。
select Employee.DepartmentId ,max(Employee.Salary) from Employee group by Employee.DepartmentId
DepartmentId | max(Employee.Salary) |
---|---|
1 | 90000 |
2 | 80000 |
注意:为什么不直接提取出员工的姓名,员工的薪水,而是只提取出了每个部门最高薪水和部门的Id呢?
例如(错误示例):
select Employee.name,max(Employee.Salary) from Employee group by Employee.DepartmentId
//注意!这是错误的写法!
group by
只能将分组的依据属性和分组后的操作属性连接起来。对于同一行的数据,并不能保持一致。例如在第二步的正确写法中,其中分组的依据属性是Employee.DepartmentId
(部门Id),而分组后的操作属性是max(Employee.Salary)
(取最大值操作),只有部门Id和最大薪水之间是可以保持正确的对应关系,除此之外和其他任何属性都无法保持正确的对应关系。
第三步:
将第二步检索出的最大值用作第一步中的where
条件。
select
Department.name as Department,
Employee.name as Employee,
Employee.Salary as Salary
from
Employee
join
Department on Department.id=Employee.departmentid
where
(employee.departmentId,employee.salary)
in
(select employee.DepartmentId ,max(employee.salary) from employee group by employee.DepartmentId )
从第一步得出的所有员工的联合数据表中,找出(部门id,薪水(最高))相匹配的员工数据,则是每个部门最高薪水员工的数据。
总结:
本题目的知识点考察主要在于表的正确连接以及group by
下高级操作的正确使用。新手很容易会犯第二步中的错误。因此,要牢记group by
输出后的数据之间正确的对应关系。
结合此题目,凡是涉及到分组求最值,分组求前n个值,以及需要输出分组后的全部属性信息等问题,都可以参考本文中的解决方法。
最后附上本问题的另一种解法,其思路是一致的。
select
Department.name as Department,
Employee.Name as Employee,
Employee.Salary as Salary
from Employee
join Department
on Employee.DepartmentId=Department.id
join (select Employee.DepartmentId,max(Employee.Salary) as Salary from Employee group by Employee.DepartmentId) as mt
where
Employee.DepartmentId=mt.DepartmentId
and Employee.Salary=mt.Salary