从子句中嵌套选择 [英] Nested Select in From Clause

问题描述

在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.

这篇关于从子句中嵌套选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！