SQL多查询结果合并及部门薪资均值、偏差计算求助

数据库表结构 #

create table dept(  
  deptno     number(2,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);
create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);

需要输出的字段 #

• 部门位置（Department Location）
• 公司平均薪资（Company average salary）
• 部门平均薪资（Department average salary）
• 部门薪资偏差（Deviation from department average salary）
• 公司薪资偏差（Deviation from company average salary）
• 员工编号（EMPNo）
• 职位（Job）
• 薪资（Salary）

解决方案SQL #

SELECT
    d.loc AS "Department Location",
    ROUND(AVG(e.sal) OVER (), 2) AS "Company average salary",
    ROUND(AVG(e.sal) OVER (PARTITION BY e.deptno), 2) AS "Department average salary",
    ROUND(e.sal - AVG(e.sal) OVER (PARTITION BY e.deptno), 2) AS "Deviation from department average salary",
    ROUND(e.sal - AVG(e.sal) OVER (), 2) AS "Deviation from company average salary",
    e.empno AS "EMPNo",
    e.job AS "Job",
    e.sal AS "Salary"
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

关键说明 #

• 表关联：使用JOIN语法关联emp和dept表，直接获取每个员工对应的部门位置，替代旧的逗号分隔关联写法，逻辑更清晰。
• 公司平均薪资：AVG(sal) OVER ()是无分区的窗口函数，计算全公司员工的薪资平均值，会自动填充到每一行。
• 部门平均薪资：AVG(sal) OVER (PARTITION BY e.deptno)按部门编号分区计算平均值，确保每行显示当前员工所属部门的薪资均值。
• 薪资偏差：直接用员工薪资分别减去部门平均、公司平均，通过ROUND函数保留两位小数，得到对应的偏差数值。

内容的提问来源于stack exchange，提问作者Kenertj