在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) Tselect *有效,但这不是我想要的.有人知道怎么做吗?
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.
更多推荐
从子句中嵌套选择
发布评论