SQL查询部门内薪资/佣金低于/高于均值人员报ORA-00979错误解决

第一题（薪资低于部门平均的员工查询） #

错误原因说明 #

你写的SQL存在2个核心问题：

• 子查询逻辑不符合需求：SELECT AVG(sal) FROM EMP计算的是全公司所有员工的平均薪资，不是题目要求的员工所在部门的平均薪资
• 多余的GROUP BY导致语法报错：Oracle要求使用GROUP BY时，SELECT后所有非聚合函数的字段必须全部放到GROUP BY子句中，你SELECT了ename、sal、deptno三个字段，只在GROUP BY写了deptno，因此触发ORA-00979错误，且该需求不需要使用分组逻辑，直接删除GROUP BY即可。

正确参考SQL #

SELECT ename, sal, deptno
FROM Emp e1
WHERE sal < (
    -- 子查询只计算当前员工所属部门的平均薪资
    SELECT AVG(sal) 
    FROM Emp e2 
    WHERE e2.deptno = e1.deptno
);

逻辑说明 #

给外层员工表起别名e1，子查询的员工表起别名e2，执行时会逐行读取e1的员工数据，每次都计算该员工所在部门的平均薪资，再对比当前员工薪资是否低于该平均值，符合条件的就会被筛选出来。 #

第二题（佣金高于部门平均的员工查询） #

逻辑和第一题完全一致，只需要替换对比字段和判断符号即可：

SELECT ename, comm, deptno
FROM Emp e1
WHERE comm > (
    SELECT AVG(comm) 
    FROM Emp e2 
    WHERE e2.deptno = e1.deptno
    -- 若题目要求计算平均时排除无佣金的员工，可去掉下方注释
    -- AND comm IS NOT NULL
);

小提示 #

AVG()聚合函数计算时会自动忽略NULL值，如果部门里有员工没有佣金（comm为NULL），默认不会计入平均的计算，你可以根据题目具体要求调整过滤条件。 #

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