从 Oracle 9i 中的嵌套查询中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/251409/
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
Select from a nested query in Oracle 9i
提问by David.Chu.ca
In MS SQL 2005 or T-SQL, you can do something like:
在 MS SQL 2005 或 T-SQL 中，您可以执行以下操作：
SELECT T.NAME, T.DATE
FROM (SELECT * FROM MyTable WHERE ....) AS T
I failed to try the similar SQL on Oracle 9i DB. In MS SQL, the nested SQL is treated as a temporary/dynamic view created on fly and destroyed afterward. How can I do the similar thing in Oracle? I really don't want to create a view to do it.
我未能在 Oracle 9i DB 上尝试类似的 SQL。在 MS SQL 中，嵌套 SQL 被视为临时/动态视图，在运行中创建并随后销毁。我怎样才能在 Oracle 中做类似的事情？我真的不想创建一个视图来做到这一点。
回答by James Curran
I believe it chokes on the "as".
我相信它会因“as”而窒息。
SELECT T.NAME, T.DATE
FROM (SELECT * FROM MyTable WHERE ....) T
should work.
应该管用。
回答by Dave Costa
The only thing you need to change is remove the keyword "AS". Oracle uses that only for column aliases (e.g. SELECT dummy AS some_name FROM dual), although even then you don't need it.
您唯一需要更改的是删除关键字“AS”。Oracle 仅将它用于列别名（例如 SELECT dummy AS some_name FROM dual），尽管即使如此您也不需要它。
回答by David Aldridge
If you really need to create a temporary, physical result set then you would do that with a subquery factoring clause:
如果您真的需要创建一个临时的物理结果集，那么您可以使用子查询分解子句来做到这一点：
with t as
(SELECT /*+ materliaze */
*
FROM MyTable
WHERE ....)
SELECT T.NAME, T.DATE
FROM T
/
It's generally not worthwhile except for specific situations in which the optimizer's accurate estimation of an intermediate result set in a query is critical to an efficient execution plan, in which case dynamic sampling can be used to see the size of the result set, or where a calculated result set is used multiple times in the rest of the query.
除了优化器对查询中中间结果集的准确估计对于高效执行计划至关重要的特定情况外，通常不值得，在这种情况下，可以使用动态采样来查看结果集的大小，或者在查询的其余部分中多次使用计算结果集。
You ought to be able to just drop the AS, and Oracle will logically merge the in-line view into the master query if it is safe to do so.
您应该能够只删除 AS，如果这样做是安全的，Oracle 会在逻辑上将内嵌视图合并到主查询中。
