SQL嵌套子查询问题：基于HR.EMPLOYEES表求平均工龄超公司均值的部门

要获取平均工龄高于全公司平均工龄的部门列表，需要分两步计算：先算出全公司的平均工龄，再按部门分组对比部门平均工龄与全公司平均。

错误分析 #

你原来的SQL存在几个问题：

• WHERE子句中不能直接使用聚合函数avg()，聚合函数需要配合GROUP BY和HAVING使用
• 子查询语法不完整，没有正确计算全公司的平均工龄，逻辑混乱

正确SQL语句 #

SELECT department_id, AVG(MONTHS_BETWEEN(SYSDATE, hire_date)) AS avg_dept_tenure
FROM hr.employees
WHERE department_id IS NOT NULL  -- 排除无部门的员工
GROUP BY department_id
HAVING AVG(MONTHS_BETWEEN(SYSDATE, hire_date)) > (
    SELECT AVG(MONTHS_BETWEEN(SYSDATE, hire_date))
    FROM hr.employees
);

语句解释 #

1. 子查询部分：SELECT AVG(MONTHS_BETWEEN(SYSDATE, hire_date)) FROM hr.employees 计算全公司所有员工的平均工龄（以月为单位）
2. 主查询部分：
   • 按department_id分组，计算每个部门的平均工龄
   • 使用HAVING子句筛选出部门平均工龄大于全公司平均的部门
   • 加上WHERE department_id IS NOT NULL是为了排除没有分配部门的员工记录

内容的提问来源于stack exchange，提问作者Елизавета Тараненко