由于列别名，oracle 中的无效标识符错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22459260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
invalid identifier error in oracle due to column alias
提问by Setsuna F. Seiei
I have a query as follows
我有一个查询如下
select t.col1,
t.col2,
(select count(col1)
from tab
where col1 = t.col1
and col2 = t.col2
) as col3
from tab t
where col3 > 1
The query gives an 'col3 invalid identifier' error.
查询给出了“col3 无效标识符”错误。
I have tried different variations defining the alias which I have given below and the error I get when I use them
我尝试了不同的变体来定义我在下面给出的别名以及我在使用它们时遇到的错误
select t.col1, t.col2, (select count(col1) from tab where col1 = t.col1 and col2 = t.col2 ) as "col3" from tab t where col3 > 1
select t.col1, t.col2, (select count(col1) from tab where col1 = t.col1 and col2 = t.col2 ) as "col3" from tab t where col3 > 1
Error: col3 invalid identifier
错误：col3 无效标识符
select t.col1, t.col2, (select count(col1) from tab where col1 = t.col1 and col2 = t.col2 ) as 'col3' from tab t where [col3] > 1
select t.col1, t.col2, (select count(col1) from tab where col1 = t.col1 and col2 = t.col2 ) as 'col3' from tab t where [col3] > 1
Error: Missing expression after where
错误：where 后缺少表达式
select t.col1, t.col2, (select count(col1) from tab where col1 = t.col1 and col2 = t.col2 ) "col3" from tab t where [col3] > 1
select t.col1, t.col2, (select count(col1) from tab where col1 = t.col1 and col2 = t.col2 ) "col3" from tab t where [col3] > 1
Error: Missing expression after where
错误：where 后缺少表达式
Please explain me what the errors are about
请向我解释错误是关于什么的
P.S. I don't know why I am unable to mark the query examples as code here. I apologize for the poor readability of those queries
PS我不知道为什么我无法在此处将查询示例标记为代码。对于这些查询的可读性差，我深表歉意
采纳答案by a_horse_with_no_name
Your main problem is that you can't access a column alias on the same "nesting level". In order to be able to use the alias you need to wrap the whole query in a derived table:
您的主要问题是您无法访问同一“嵌套级别”上的列别名。为了能够使用别名，您需要将整个查询包装在派生表中：
select *
from (
select t.col1,
t.col2,
(select count(col1)
from tab
where col1 = t.col1
and col2 = t.col2
) as col3
from tab t
)
where col3 > 1
Your "numbered" examples would not work for two reasons: first for the above reason and secondly because identifiers that are quoted using double quotes become case-sensitive. So "col3"is a different column name than "Col3". As Oracle folds unquoted identifiers to uppercase (following the requirements of the SQL standard) col3would be equivalent to "COL3"
您的“编号”示例不起作用有两个原因：首先是上述原因，其次是因为使用双引号引用的标识符区分大小写。所以，"col3"是不是不同的列名"Col3"。由于 Oracle 将不带引号的标识符折叠为大写（遵循 SQL 标准的要求）col3将等效于"COL3"
Finally: [col3]is an invalid identifier in SQL regardless if you use it as a column alias or not. Identifiers must be quoted using double quotes. Those square brackets are invalid in SQL
最后：[col3]是 SQL 中的无效标识符，无论您是否将其用作列别名。标识符必须使用双引号引起来。那些方括号在 SQL 中无效
回答by Wolf
You cannot use a column alias in a WHEREclause, only in an ORDER BYclause. You have two options.
不能在WHERE子句中使用列别名，只能在ORDER BY子句中使用。你有两个选择。
One, you can wrap you entire query in another select and then filter on col3:
一个，您可以将整个查询包装在另一个选择中，然后在 col3 上进行过滤：
select * from (
select t.col1,
t.col2,
(select count(col1)
from tab
where col1 = t.col1
and col2 = t.col2
) as col3
from tab t)
where col3 > 1;
Or you can repeat the scalar subquery in the WHEREclause:
或者您可以在WHERE子句中重复标量子查询：
select t.col1,
t.col2,
(select count(col1)
from tab
where col1 = t.col1
and col2 = t.col2
) as col3
from tab t
where (select count(col1)
from tab
where col1 = t.col1
and col2 = t.col2
) > 1;
I suggest option 1 myself.
我自己建议选项1。
