为何SQL语句中AVERAGE是无效标识符？ORA-00904错误排查

错误原因 #

你在WHERE子句中使用了SELECT里定义的别名average，但SQL的执行逻辑是先执行WHERE过滤原始行，再进行GROUP BY分组，最后计算聚合函数并生成SELECT里的别名。所以当执行WHERE的时候，average这个别名还没被创建，Oracle就会抛出"invalid identifier"的错误。

正确写法 #

要筛选分组后的聚合结果，得用HAVING子句——它是在GROUP BY分组完成后执行的，此时聚合值已经计算完成，可以直接用来做条件判断。

写法一（推荐，兼容多数数据库） #

SELECT 
    places.name AS place_name, 
    ROUND(AVG(frequency), 2) AS average
FROM 
    places, visits
WHERE 
    places.place_id = visits.place_id 
GROUP BY 
    places.name
HAVING 
    AVG(frequency) > (SELECT AVG(frequency) FROM visits);

写法二（Oracle支持用别名） #

Oracle允许在HAVING里直接引用SELECT的别名，所以也可以写成这样：

SELECT 
    places.name AS place_name, 
    ROUND(AVG(frequency), 2) AS average
FROM 
    places, visits
WHERE 
    places.place_id = visits.place_id 
GROUP BY 
    places.name
HAVING 
    average > (SELECT AVG(frequency) FROM visits);

更规范的JOIN写法 #

建议把旧的逗号连接表的方式改成显式JOIN，逻辑更清晰：

SELECT 
    p.name AS place_name, 
    ROUND(AVG(v.frequency), 2) AS average
FROM 
    places p
JOIN visits v ON p.place_id = v.place_id
GROUP BY 
    p.name
HAVING 
    AVG(v.frequency) > (SELECT AVG(frequency) FROM visits);

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