SQL 从子句中嵌套选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9025823/
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
Nested Select in From Clause
提问by MildC
In SQL Server,
在 SQL Server 中，
you can write nested SQL like this:
您可以像这样编写嵌套 SQL：
SELECT T.con
FROM (SELECT count(*) as "con" FROM EMP) AS T
In such way, I can get a temp table T that can be nested into other query.
通过这种方式，我可以获得一个可以嵌套到其他查询中的临时表 T。
But I cannot do the same thing in oracle SQL
但是我不能在 oracle SQL 中做同样的事情
It gives me ORA-01747:invalid column
它给了我 ORA-01747:invalid column
SELECT *
FROM (SELECT count(*) as "con" FROM EMP) T
select * works, but it's not what I want. Anybody knows how to do it?
select * 有效，但这不是我想要的。有人知道怎么做吗？
回答by Justin Cave
The query you posted works fine for me whether I specify the alias explicitly or whether I specify a *. Can you post the exact query you're running when you get the exception?
无论是明确指定别名还是指定*. 当您收到异常时，您可以发布您正在运行的确切查询吗？
SQL> SELECT *
2 FROM (SELECT count(*) as "con" FROM EMP) T;
con
----------
14
SQL> ed
Wrote file afiedt.buf
1 SELECT "con"
2* FROM (SELECT count(*) as "con" FROM EMP) T
SQL> /
con
----------
14
My guess would be that you're trying to select conwithout the double-quotes. If you use a double-quoted identifier in Oracle, you're telling Oracle that you want the identifier to be case-sensitive which, in turns, means that you always have to refer to it in a case-sensitive manner and you have to enclose the column name in double quotes every time. I would strongly advise against using case sensitive identifiers in Oracle.
我的猜测是您试图在con没有双引号的情况下进行选择。如果您在 Oracle 中使用双引号标识符，则是在告诉 Oracle 您希望该标识符区分大小写，这反过来意味着您必须始终以区分大小写的方式引用它，并且您必须每次都用双引号将列名括起来。我强烈建议不要在 Oracle 中使用区分大小写的标识符。
回答by Zsolt Botykai
Well, ORA-01747 occurs usually with reserved words but as far as I know conis not a reserved word. Anyway, try:
好吧，ORA-01747 通常与保留字一起出现，但据我所知con不是保留字。无论如何，请尝试：
select t.*
from (select count(*) count_emp from emp) t
