Oracle SQL嵌套分组函数报错及ROWNUM查询结果异常咨询

可正确返回结果（job_id为PU_CLERK，对应平均薪资2700）的参考实现如下：

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                      FROM employees
                      GROUP BY job_id); 

疑问1：为什么SELECT job_id, MIN(AVG(salary)) FROM employees GROUP BY job_id会抛出"not a single-group group function"错误？ #

SQL的分组聚合是严格分层计算的，单层GROUP BY只能支持一层聚合运算，不存在嵌套聚合的自动推导。

• 当你写GROUP BY job_id时，数据库的计算逻辑很明确：把员工数据按job_id拆成互不重叠的分组，每个分组最终返回一行，这一层允许的聚合函数（比如AVG(salary)、COUNT(*)这类），计算范围都是单个分组内部的原始员工行。
• 而MIN(AVG(salary))是实打实的两层嵌套聚合：内层AVG(salary)是第一次按job_id分组后，每个组算出的平均薪资；外层MIN要做的，是把「所有分组算出来的平均薪资」当成一个新的结果集，再做一次取最小值的聚合——这本质上需要第二次分组计算，但你写的语句里只有一层按job_id的GROUP BY，没有针对第一次聚合结果做二次聚合的逻辑，数据库根本判断不了外层MIN的计算范围，自然会抛错。
• 你之前搜到的报错解决方案，只覆盖了单层聚合下SELECT列和GROUP BY不匹配的场景，根本没提嵌套聚合的分层规则，所以哪怕你把job_id加到GROUP BY里也没用，毕竟job_id是第一次分组的维度，管不到第二层聚合的计算。

疑问2：加了ROWNUM = 1之后为什么返回的结果不是排序后的最低平均薪资岗位？ #

核心原因是Oracle里SQL各子句的执行有固定优先级，ROWNUM的赋值和过滤远早于ORDER BY排序。
你写的带ROWNUM的语句，实际执行顺序是：

1. 先读employees全表数据
2. 执行WHERE条件过滤：碰到读到的第一行员工数据，就给它标上ROWNUM=1，满足ROWNUM=1的条件留下，剩下所有还没读的行直接全部丢弃
3. 对留下来的这1行数据，按job_id分组算平均薪资
4. 最后对这1行结果做ORDER BY排序，返回结果

这种逻辑下算出来的结果，和全量分组排序后的第一行没有任何关系，返回SH_CLERK、平均薪资2600纯粹是因为这行数据在全表扫描时刚好被第一个读到而已。

额外提一句：你之前不带ROWNUM的排序语句本身写法就有问题：ORDER BY(salary)是按原始表的salary列排序，不是按你计算的AVG(salary)排序，最后结果刚好和预期一致完全是巧合，正确的排序写法应该是ORDER BY AVG(salary)。
不管是用ROWNUM还是FETCH语法取排序后的第一行，都必须先把分组、排序的逻辑包在子查询里，等分组、排序全部完成之后，再在外层做行数限制，才能拿到正确结果。

疑问3：为什么SELECT MIN(AVG(salary)) FROM employees GROUP BY job_id能查到最低平均薪资值，却返回不了对应的job_id？ #

本质是SELECT返回的列必须和结果集的粒度完全匹配。
这条语句的执行逻辑是：先按job_id分组算出每个岗位的平均薪资，再对所有岗位的平均薪资取最小值，最终返回的是1行全局聚合结果——也就是全公司最低的岗位平均薪资2700，这个结果的粒度是「全公司所有岗位的统计值」，而job_id的粒度是「单个岗位」，两者粒度完全不匹配。
数据库不会自动帮你把全局最小值关联到对应的job_id，毕竟现实中可能存在多个岗位平均薪资同时等于最小值的情况，直接返回单个job_id会有逻辑歧义，所以语法层面就不支持这种写法。

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