Oracle SQL: Multiple With statement

Question

我正在创建一个巨大的SQL查询：

Select * from (With tab1 AS ( Select * from abc)
,tab2 AS (select * from cde)
,tab3 AS (select * from tab2)
.
.
.
,tabz AS (select a, b from xyz
UNION
select a, b from cde)
Select tab1.*, tab3.* from 
tab1
LEFT OUTER JOIN tab2 ON tab1.a = tab2.b
...
LEFT OUTER JOIN tabz on tab1.a = tabz.a) A

现在使用上述作为 1 个表，我需要创建另一个长 SQL 来计算其他表表的百分比和其他内容。

假设上表是 A 然后

Select bbb.a,bbb.b from bbb
JOIN A ON bbb.a = A.a and then name it as B

然后终于加入A LEFT OUTER JOIN B.

这是一个庞大的查询，我知道我们不能有嵌套的 WITH 语句。有没有人有简单的方法来完成这个？还是任何建议？ 我只需要使用 oracle SQL 查询来完成此操作。

Answer 1

我认为您可以将查询改写为：

WITH
tab1 AS (select * from abc)
,tab2 AS (select * from cde)
,tab3 AS (select * from tab2)
.
.
.
,tabz AS (select a, b from xyz
UNION
select a, b from cde)
,a as (
Select tab1.*, tab3.* 
from tab1
LEFT OUTER JOIN tab2 ON tab1.a = tab2.b
...
LEFT OUTER JOIN tabz on tab1.a = tabz.a
),
b as (
Select bbb.a,bbb.b from bbb JOIN A ON bbb.a = A.a
)
select * 
from a 
left join b on ...