SELECT主查询中不能直接用聚合函数套子查询，因为聚合函数必须在GROUP BY上下文或标量子查询中使用，而子查询若返回多行则违反标量要求，需通过分组子查询预计算或WITH子句提取聚合结果。
为什么 SELECT 主查询里不能直接用聚合函数套子查询？
因为 SQL 执行顺序是先跑子查询、再处理外层 SELECT，而聚合函数（比如 COUNT()、AVG()）必须配合 GROUP BY 或出现在聚合上下文中；如果子查询返回多行，外层又没分组，数据库会直接报错：ERROR: subquery must return only one row 或类似提示。
常见场景是想查“每个部门平均工资高于公司平均工资的部门”，但写成 WHERE AVG(salary) > (SELECT AVG(salary) FROM emp) 会失败——AVG() 不能裸放在 WHERE 里，更不能跨作用域直接比较。
- 聚合函数只能出现在
SELECT列表、HAVING子句或标量子查询中 - 子查询若用于表达式（如
WHERE右侧），必须是标量——即严格返回 1 行 1 列 - 想按组做比较，就得让分组逻辑在子查询里先完成，而不是指望外层“边分组边套聚合”
用分组子查询预计算：把聚合结果当“临时表”用
核心思路是把需要复用的聚合结果（比如各部门平均工资、各用户订单总数）提前算好，放进一个子查询，再和主表 JOIN 或用 IN/= ANY 关联。这样既避开语法限制，又保持逻辑清晰。
例如查“平均工资超过全公司均值的部门”：
SELECT dept_name, dept_avg FROM ( SELECT dept_id, dept_name, AVG(salary) AS dept_avg FROM dept d JOIN emp e ON d.id = e.dept_id GROUP BY dept_id, dept_name ) dept_stats WHERE dept_avg > (SELECT AVG(salary) FROM emp);
- 子查询
(SELECT ... GROUP BY ...)先完成分组聚合，产出确定的单值列dept_avg - 外层不再动聚合函数，只做普通数值比较，完全合法
- 注意子查询里
GROUP BY必须包含所有非聚合字段（如dept_name），否则 PostgreSQL 等严格模式会报错
WITH 替代嵌套：可读性更强，但要注意物化行为
用 WITH 把预计算抽出来，比多层括号嵌套更易维护，尤其当同一聚合结果要被多次引用时。
等价写法：
WITH dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id ), company_avg AS ( SELECT AVG(salary) AS avg_salary FROM emp ) SELECT d.name, da.avg_salary FROM dept d JOIN dept_avg da ON d.id = da.dept_id CROSS JOIN company_avg ca WHERE da.avg_salary > ca.avg_salary;
- PostgreSQL 默认可能物化
WITH查询结果（即使只用一次），而 Oracle/MySQL 8.0+ 默认不物化；性能敏感时得看执行计划 -
CROSS JOIN company_avg是安全的，因为company_avg恒为单行；换成JOIN可能因空结果导致整行丢失 - 别在
WITH子句里引用外部查询的列——它独立于外层作用域
容易忽略的边界：NULL 和空分组
聚合函数对空集返回 NULL（COUNT 返回 0 除外），而 NULL 参与比较（如 >）结果恒为 UNKNOWN，会导致该行被过滤掉——这未必是预期行为。
- 部门没员工时，
AVG(salary)是NULL，WHERE dept_avg > company_avg不成立，该部门直接消失 - 若需保留空部门，得显式判断：
WHERE dept_avg > company_avg OR dept_avg IS NOT NULL（视业务而定） - 用
COALESCE(AVG(salary), 0)强制转 0 要谨慎——0 和 NULL 语义完全不同，可能掩盖数据缺失问题
