如何创建MySQL视图统计各部门历史平均薪资并修复现有报错代码

核心错误原因 #

• 表关联逻辑错误：departments（部门表）和salaries（薪资表）没有公共关联字段，直接使用NATURAL JOIN会生成笛卡尔积，既不符合业务逻辑也会导致计算异常或报错。两个表需要通过中间表dept_emp（员工部门关联表）关联，关联逻辑为：departments通过dept_no字段关联dept_emp，dept_emp再通过emp_no字段关联salaries。
• 删除语句无效：开头删除的视图名是historical_average，但实际要创建的视图名为hist_avg_salary，该语句无法规避新视图的重复创建问题。
• 注释格式兼容性问题：代码中使用#作为单行注释符，部分SQL执行环境可能不兼容，建议统一使用-- 作为单行注释符。

可正常运行的修正代码 #

-- 先删除目标视图，避免重复创建报错
DROP VIEW IF EXISTS hist_avg_salary;

CREATE VIEW hist_avg_salary AS
SELECT 
  dep.dept_name AS DepartmentName, 
  AVG(s.salary) AS AVG_Salary
FROM departments AS dep
-- 通过中间表关联部门和薪资
INNER JOIN dept_emp AS de ON dep.dept_no = de.dept_no
INNER JOIN salaries AS s ON de.emp_no = s.emp_no
GROUP BY dep.dept_name
ORDER BY AVG_Salary DESC;

-- 查询视图
SELECT * FROM hist_avg_salary;
-- 查看当前库下所有视图
SHOW FULL TABLES IN employees WHERE TABLE_TYPE LIKE 'VIEW';

可选优化（按需使用） #

如果需要精确统计员工在对应部门任职期间的薪资，避免员工跨部门调动时的薪资被错误计入其他部门，可以在关联时增加时间匹配条件：

-- 在JOIN语句后补充WHERE条件
WHERE s.from_date BETWEEN de.from_date AND de.to_date

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