从子句中嵌套选择

本文介绍了从子句中嵌套选择的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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