了解 Oracle 别名 - 为什么除非包含在第二个查询中，否则查询中无法识别别名？

Understanding Oracle aliasing - why isn't an alias not recognized in a query unless wrapped in a second query?

提问by simon

I have a query

我有一个疑问

SELECT COUNT(*) AS "CNT",
       imei
FROM   devices  

which executes just fine. I want to further restrict the query with a WHERE statement. The (humanly) logical next step is to modify the query followingly:

执行得很好。我想用 WHERE 语句进一步限制查询。（人为）合乎逻辑的下一步是按以下方式修改查询：

SELECT COUNT(*) AS "CNT",
       imei
FROM   devices
WHERE  CNT > 1 

However, this results in a error message ORA-00904: "CNT": invalid identifier. For some reason, wrapping the query in another query produces the desired result:

但是，这会导致错误消息ORA-00904: "CNT": invalid identifier。出于某种原因，将查询包装在另一个查询中会产生所需的结果：

SELECT *
FROM   (SELECT COUNT(*) AS "CNT",
               imei
        FROM   devices
        GROUP  BY imei)
WHERE  CNT > 1  

Why does Oracle not recognize the alias "CNT" in the second query?

为什么 Oracle 在第二个查询中无法识别别名“CNT”？

回答by Craig

Because the documentationsays it won't:

因为文档说它不会：

Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

为列表达式指定别名。Oracle 数据库将在结果集的列标题中使用此别名。AS 关键字是可选的。别名在查询期间有效地重命名选择列表项。别名可以在 order_by_clause 中使用，但不能在查询中的其他子句中使用。

However, when you have an inner select, that is like creating an inline view where the column aliases take effect, so you are able to use that in the outer level.

但是，当您有一个内部选择时，这就像创建一个列别名生效的内联视图，因此您可以在外部级别使用它。

回答by Datajam

The simple answer is that the ASclause defines what the column will be called in the result, which is a different scope than the query itself.

简单的答案是该AS子句定义了将在结果中调用列的内容，这与查询本身的范围不同。

In your example, using the HAVINGclause would work best:

在您的示例中，使用该HAVING子句效果最好：

SELECT COUNT(*) AS "CNT",
       imei
FROM   devices
GROUP  BY imei
HAVING COUNT(*) > 1

回答by Tony Andrews

I would imagine because the alias is not assigned to the result column until after the WHERE clause has been processed and the data generated. Is Oracle different from other DBMSs in this behaviour?

我想是因为在处理 WHERE 子句并生成数据之前，别名不会分配给结果列。Oracle 在这种行为上是否与其他 DBMS 不同？

回答by David Tonhofer

To summarize, this little gem explains:

总而言之，这个小宝石解释了：

10 Easy Steps to a Complete Understanding of SQL

完整理解 SQL 的 10 个简单步骤

A common source of confusion is the simple fact that SQL syntax elements are not ordered in the way they are executed. The lexical ordering is:

SELECT [ DISTINCT ]
FROM
WHERE
GROUP BY
HAVING
UNION
ORDER BY

For simplicity, not all SQL clauses are listed. This lexical ordering differs fundamentally from the logical order, i.e. from the order of execution:

FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY

一个常见的混淆来源是一个简单的事实，即 SQL 语法元素没有按照它们的执行方式排序。词法顺序是：

SELECT [ DISTINCT ]
FROM
WHERE
GROUP BY
HAVING
UNION
ORDER BY

为简单起见，并未列出所有 SQL 子句。这种词汇顺序与逻辑顺序有根本的不同，即与执行顺序不同：

FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY

As a consequence, anything that you label using "AS" will only be available once the WHERE, HAVINGand GROUP BYhave already been performed.

因此，您使用“AS”标记的任何内容仅在WHERE,HAVING并且GROUP BY已经执行后才可用。