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