查询每位员工薪资及所在部门平均薪资的SQL语句问题

我尝试用以下SQL语句查询每位员工的薪资及其所在部门的平均薪资，但无法得到预期结果：

SELECT 
    job_id, emp_name, salary, AVG(SALARY) AS AVERAGE_SALARY 
FROM 
    employees 
GROUP BY 
    emp_name, department_id;

数据表信息 #

employees表包含字段：employee_id、emp_name、job_id、manager_id、hire_date、salary、commission_pct、department_id，部分数据示例：

• employee_id: 100，emp_name: Steven，job_id: AD_PRES，salary: 24000，department_id: 90
• employee_id: 101，emp_name: Neena，job_id: AD_VP，salary: 17000，department_id: 90
• employee_id: 102，emp_name: Lex，job_id: AD_VP，salary: 17000，department_id: 90
• employee_id: 103，emp_name: Alexander，job_id: IT_PROG，salary: 9000，department_id: 60

预期输出 #

展示每位员工的job_id、emp_name、salary，以及该员工所在部门的平均薪资（同一部门的员工对应的AVERAGE_SALARY值相同），示例：

实际输出 #

得到的结果中，AVERAGE_SALARY等于员工自身的salary，因为分组方式错误导致每个组只有单个员工，示例：

问题原因及解决方案 #

错误原因 #

原SQL的分组逻辑错误：GROUP BY emp_name, department_id会把每个员工（因emp_name唯一）单独分成一组，此时AVG(salary)计算的就是单个员工的薪资，自然等于员工自身的salary。另外，SELECT中的job_id不在GROUP BY子句中，在严格SQL模式下会直接报错，非严格模式下返回的结果也不可靠。

正确解决方案 #

要获取部门平均薪资，需要先按department_id分组计算每个部门的平均薪资，再通过关联查询将部门平均薪资匹配到每个员工：

方法1：子查询关联

SELECT 
    e.job_id, 
    e.emp_name, 
    e.salary, 
    dept_avg.AVERAGE_SALARY
FROM 
    employees e
JOIN (
    SELECT 
        department_id, 
        AVG(salary) AS AVERAGE_SALARY
    FROM 
        employees
    GROUP BY 
        department_id
) dept_avg ON e.department_id = dept_avg.department_id;

方法2：窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、Oracle等支持窗口函数的数据库）

SELECT 
    job_id, 
    emp_name, 
    salary, 
    AVG(salary) OVER (PARTITION BY department_id) AS AVERAGE_SALARY
FROM 
    employees;

窗口函数PARTITION BY department_id会按部门分组，为每个员工计算其所在部门的平均薪资，无需手动关联子查询，代码更简洁高效。

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