oracle 子查询中的公用表表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6812393/
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
Common Table Expression in Sub-Query
提问by Kapil
I would request for help in understanding which all RDBMS from Oracle, DB2, Sybase support a common table expression (CTE) in a sub-query. I am aware that PostgreSQL does while MS SQL Server does not.
我会请求帮助了解哪些来自 Oracle、DB2、Sybase 的所有 RDBMS 支持子查询中的公共表表达式 (CTE)。我知道 PostgreSQL 有，而 MS SQL Server 没有。
SELECT a.*, b.*
FROM (WHERE aa as (
<<select_query>),
SELECT *
FROM aa
WHERE <<criteria>>
) as a
LEFT JOIN (
WITH bb as (
<<select_query>
),
select * from bb inner join tbl_c on <<innerjoin>> where <<criteria>>
) as b
on <<join_expr>>
I am unable to define the with clause outside the sub-queries - both the queries are dynamically generated w.r.t. the columns, criteria, security, etc. Also, the above query itself may be used in another query as a sub-query. In summary, the principle is dynamically generated views, re-usable later. Some queries may have upto 10-12 such dynamic views being merged together as well. The problem is that the application is supposed to be database-agnostic at least so far as PG, Oracle & DB2 are concerned and features not supported by one are not implemented at all.
我无法在子查询之外定义 with 子句 - 两个查询都是动态生成的，列、条件、安全性等。此外，上述查询本身可以在另一个查询中用作子查询。综上所述，原理是动态生成视图，以后可以复用。一些查询可能有多达 10-12 个这样的动态视图被合并在一起。问题在于，至少就 PG、Oracle 和 DB2 而言，该应用程序应该与数据库无关，并且根本没有实现不支持的功能。
回答by DCookie
Yes, you can use CTE's in subqueries in Oracle. From the Oracle 11g docs:
是的，您可以在 Oracle 的子查询中使用 CTE。来自Oracle 11g 文档：
You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries. For recursive subquery factoring, the query name is even visible to the subquery that defines the query name itself.
您可以在任何顶级 SELECT 语句和大多数类型的子查询中指定此子句。查询名称对主查询和所有后续子查询可见。对于递归子查询分解，查询名称甚至对定义查询名称本身的子查询可见。
As an example, this works in Oracle:
例如，这适用于 Oracle：
SELECT a.*, b.*
FROM (WITH aa AS
(
SELECT LEVEL l1, mod(level, 5) m1 FROM dual CONNECT BY LEVEL < 50
)
SELECT * FROM aa WHERE m1 < 3) a LEFT JOIN
(WITH bb AS
(
SELECT LEVEL l2, mod(level, 5) m2 FROM dual CONNECT BY LEVEL < 50
)
SELECT * FROM bb WHERE m2 BETWEEN 1 AND 4) b
ON a.l1 = b.l2;
回答by Lukas Eder
That's not directly an answer to your question, but maybe you can think about this:
这不是您问题的直接答案，但也许您可以考虑一下：
SQL Server seems to limit the semantics (not necessarily the syntax) of SQL where it makes sense to do so. For instance, you cannot have a subquery with an ORDER BYclause, if you don't also specify a TOP nclause. This makes sense, as ordered subqueries are pointless unless they have limits. Other RDBMS allow for such pointlessness.
SQL Server 似乎在有意义的地方限制了 SQL 的语义（不一定是语法）。例如ORDER BY，如果不指定子句，则子查询不能带有TOP n子句。这是有道理的，因为有序子查询是没有意义的，除非它们有限制。其他 RDBMS 允许这种毫无意义的情况。
In your case (that's just a guess), having CTE's in subqueries only makes limited sense, because you can rewrite your whole query in a way that the CTE's are declared at the top-most level. The only difference you'll have is the scope and maybe the readability of each declaration.
在您的情况下（这只是一个猜测），在子查询中使用 CTE 的意义有限，因为您可以以 CTE 在最顶层声明的方式重写整个查询。您将拥有的唯一区别是每个声明的范围和可读性。
On the other hand, CTE's allow for recursive queries, which might be very hard to apply when CTE's are declared in subqueries...
另一方面，CTE 允许递归查询，当在子查询中声明 CTE 时，这可能很难应用......
Since you need to implement database-agnostic SQL, I recommend you do not make heavy use of CTE's yet. If CTE's are simple, you can always rewrite them as simple views...
由于您需要实现与数据库无关的 SQL，因此我建议您不要大量使用 CTE。如果 CTE 很简单，你总是可以将它们重写为简单的视图......
回答by user3081814
The newer Microsoft SQL Server versions do support CTE's.
较新的 Microsoft SQL Server 版本确实支持 CTE。
回答by David Aldridge
While PostgreSQL supports CTE's, they are an optimisation barrier which prevents predicate or join pushing into the CTE query. This makes them less effective in many cases than a simple subquery.
虽然 PostgreSQL 支持 CTE，但它们是一个优化障碍，可防止谓词或连接推入 CTE 查询。这使得它们在许多情况下不如简单的子查询有效。
