各位大神帮帮忙，谢谢

面试题~~兄台厉害了，待老夫瞧一瞧

用 left join sql 就不写了

估计你应该面试完了：现在给一个我的猜想：

select DISTINCT deptid,staffName , salary , areaName, deptName
from （select * from staff st
left join department dt on st.deptid = dt.deptid
left join area at on at.areaid = bt.areaid
order by st.deptid asc ,st.salary desc）

这个修正版
select staffName , salary , areaName, deptName
from staff st left join department dt on st.deptid = dt.deptid
left join area at on at.areaid = bt.areaid
where salary = (select max(salary) from staff where deptid = st.deptid)

/*
分析思路：
1.题目要求各部门总工资最高的部门，那么可以先建立临时表求出总工资最高的部门
2.其次求这个部门下的工资最高的员工的信息
*/
--各部门总工资
CREATE TABLE TEMP_1
AS
SELECT DEPTID,SUM(SALARY) AMT
FROM STAFF
GROUP BY DEPTID
;

--得到最高总工资的部门下的所有员工信息
CREATE TABLE TEMP_2
AS
SELECT A.STAFFNAME,A.SALARY,C.AREANAME,B.DEPTNAME
, ROW_NUMBER() OVER(PARTITION BY A.STAFFID ORDER BY A.SALARY) RK
FROM STAFF A
LEFT JOIN DEPARTMENT B ON A.DEPTID = B.DEPTID
LEFT JOIN AREA C ON B.AREAID = C.AREAID
JOIN (SELECT DEPTID,AMT
, ROW_NUMBER() OVER(PARTITION BY DEPTID ORDER BY AMT DESC) RN
FROM TEMP_1
) B ON A.DEPTID = B.DEPTID AND B.RN = 1
;

--得出答案，该部门最高工资的员工：
SELECT STAFFNAME,SALARY,AREANAME,DEPTNAME
FROM TEMP_2
WHERE RK = 1
;