问题描述
在SQL Server中,
In SQL Server,
您可以像这样编写嵌套的SQL:
you can write nested SQL like this:
SELECT T.con
FROM (SELECT count(*) as "con" FROM EMP) AS T
这样,我可以获得一个可以嵌套到其他查询中的临时表T.
In such way, I can get a temp table T that can be nested into other query.
但是我不能在oracle SQL中做同样的事情
But I cannot do the same thing in oracle SQL
它给了我ORA-01747:invalid column
SELECT *
FROM (SELECT count(*) as "con" FROM EMP) T
select *有效,但这不是我想要的.有人知道怎么做吗?
select * works, but it's not what I want. Anybody knows how to do it?
推荐答案
无论我是显式指定别名还是指定*
,您发布的查询都对我有效.遇到异常时,您可以发布正在运行的确切查询吗?
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
我的猜测是您正在尝试选择con
而不使用双引号.如果在Oracle中使用双引号标识符,则表示Oracle希望标识符区分大小写,这反过来意味着您必须始终以区分大小写的方式引用该标识符,并且必须列名每次都用双引号引起来.我强烈建议不要在Oracle中使用区分大小写的标识符.
My guess would be that you're trying to select con
without 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.
这篇关于从子句中嵌套选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!