oracle – 子查询中的公用表表达式

发布时间：2020-12-12 15:14:45 所属栏目：百科来源：网络整理

导读：我请求帮助理解来自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 (

我请求帮助理解来自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>>

我无法在子查询之外定义with子句 – 两个查询都是动态生成的w.r.t.列,标准,安全性等
此外,上述查询本身可以在另一个查询中用作子查询.
总之,原理是动态生成的视图,以后可以重用.一些查询也可能将多达10-12个这样的动态视图合并在一起.
问题是应用程序应该与数据库无关,至少就PG,Oracle和Oracle而言. DB2受到关注,并且一个不支持的功能根本没有实现.

是的,您可以在Oracle的子查询中使用CTE.从 Oracle 11g docs：

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.

例如,这适用于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,5) m2 FROM dual CONNECT BY LEVEL < 50
         )
         SELECT * FROM bb WHERE m2 BETWEEN 1 AND 4) b
        ON a.l1 = b.l2;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!