这题是一道子查询,要先明确步骤和思路,之后就好写了
首先,题目给出了两张表,分别是Employee(E)和Department(D)
要求在每个部门里的最高的工资,可以先求每个部门中,工资的最大值
即,使用E表连接D表,让每个员工都带上部门id,然后使用聚合函数GROUP BY和MAX,求出部门id,部门名称,最高薪资
设该子查询生成的为T表
再用部门id和工资的值,到E表中去匹配
即,使用E表连接T表,连接条件为部门id和薪资,注意使用WHERE去除非最高薪资的
select
T4.name as Department
,T3.name AS Employee
,T4.salary as Salary
from Employee T3
left join
(
select
T2.id
,T2.name
,max(salary) as salary
from Employee T1
Left join Department T2
on T1.departmentId = T2.id
group by T2.name
) AS T4
on T3.salary = T4.salary and T3.departmentId = T4.id
where T4.id is not null