题目如下:
Employee
表包含所有员工信息,每个员工有其对应的 Id
, salary
和 departmentId
。
+----+-------+--------+--------------+
| 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 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解题思路
两种方式:
- 使用子查询进行过滤
- 使用join进行合并
使用子查询进行过滤(执行用时: 134 ms)
select d.Name Department,e.Name Employee,e.Salary Salary
from Employee e,Department d
where
e.DepartmentId=d.Id
and
(e.DepartmentId,e.Salary) #返回两列
in (
select DepartmentId,max(Salary) from Employee group by DepartmentId
)
返回结果:
{“headers”:[“Department”,“Employee”,“Salary”],
“values”:[[“IT”,“Jim”,90000],[“Sales”,“Henry”,80000],[“IT”,“Max”,90000]]}
使用join联表,再过滤(执行用时: 129 ms)
先使用join查询出每个部门最高工资的部门名称、薪水
select DepartmentId,
d.name as Name ,
max(Salary) as Salary from Employee e
join Department d on d.Id = e.DepartmentId
group by DepartmentId
{“headers”:[“DepartmentId”,“Name”,“Salary”],
“values”:[[1,“IT”,90000],[2,“Sales”,80000]]}
再与Employee
表进行联表查询,得出员工姓名
select
t.Name as Department,
e.Name as Employee,
e.Salary as Salary
from(
select DepartmentId,
d.name as Name ,
max(Salary) as Salary from Employee e
join Department d on d.Id = e.DepartmentId
group by DepartmentId) as t # 表t查出 [[1,"IT",90000],[2,"Sales",80000]]
join Employee e
on t.DepartmentId = e.DepartmentId and e.Salary = t.Salary # 连接条件(过滤)
得出正确结果:
{“headers”:[“Department”,“Employee”,“Salary”],
“values”:[[“IT”,“Jim”,90000],[“Sales”,“Henry”,80000],[“IT”,“Max”,90000]]}