SQL查询报ORA-00904无效标识符及列定义模糊错误排查

问题根因排查 #

你遇到的两个报错分别对应两个SQL语法错误：

• ORA-00904 STAY_COUNT无效标识符：SQL的执行顺序中，WHERE子句执行优先级高于SELECT子句，你在SELECT里定义的别名stay_count在WHERE执行阶段还未生成，自然无法识别；且聚合函数的结果筛选不能放在WHERE中，必须使用HAVING子句。
• 列定义模糊：使用count这类聚合函数时，所有在SELECT中出现的非聚合列，必须全部放到GROUP BY子句中声明分组规则，否则Oracle无法确定列的分组对应关系，就会抛出该错误。

修正后SQL #

SELECT c.customer_id, 
       c.last_name, 
       rm.room_number, 
       count(r.reservation_id) AS stay_count
FROM customer c
INNER JOIN reservation r
    ON c.customer_id = r.customer_id
INNER JOIN reservation_details rd
    ON r.reservation_id = rd.reservation_id
INNER JOIN room rm
    ON rd.room_id = rm.room_id
WHERE rm.location_id = 1
GROUP BY c.customer_id, c.last_name, rm.room_number
HAVING count(r.reservation_id) > 1
ORDER BY stay_count;

说明：如果location_id字段是字符串类型，可以保留原来的引号写为rm.location_id = '1'；HAVING子句直接写聚合表达式是为了兼容所有Oracle版本，12c及以上版本也可以直接写HAVING stay_count > 1。

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