如何解决Oracle中INSERT INTO SELECT语句的组函数报错问题

错误原因 #

你遇到的组函数报错核心是聚合函数不能直接放在WHERE子句中使用：
SQL执行顺序为FROM/JOIN -> WHERE -> GROUP BY -> 聚合计算 -> HAVING -> SELECT -> ORDER BY，WHERE阶段还没执行分组和聚合运算，所以无法识别count(w.pid)这个聚合结果，聚合结果的过滤必须使用HAVING子句，且需要先对非聚合字段分组。

修正后的代码 #

INSERT INTO Promotion_list (empID, name, salary, divisionName)
SELECT e.empID, e.name, e.salary, d.dname
FROM Employee e
INNER JOIN Division d ON e.DID = d.DID
INNER JOIN Workon w ON w.EmpID = e.empID
WHERE e.salary < (SELECT AVG(salary) FROM Employee)
GROUP BY e.empID, e.name, e.salary, d.dname
HAVING COUNT(w.PID) >= 2;

代码说明 #

• 保留WHERE子句过滤薪资低于公司平均薪资的员工，这个过滤在分组前执行，可以减少后续分组计算的数据量
• 新增GROUP BY子句，按查询的非聚合字段分组，才能统计每个员工参与的项目数量
• 把count(w.pid) >= 2的条件移到HAVING子句中，在分组聚合后过滤出参与项目数≥2的员工

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