sql - 固定表分层查询的Union All替代方案

Question

下面的查询需要时间，有没有联合所有的替代方案，我们可以在单个查询中实现吗

仅提供了 3 个表的示例数据，因此很容易被低估。

 --- GETTING FIRST LEVEL ---
select A,B,C, 'PRODUCT' from PRODUCT PR   
left outer join SPB B on B.PRODUCT_id=PR.id
left outer join SPC C on C.PRODUCT_id=PR.id AND C.SPB_ID IS NULL
left outer join SPD D on D.PRODUCT_id=PR.id AND D.SPB_ID IS NULL AND D.SPC_ID IS NULL
LEFT OUTER JOIN SPE E ON E.PRODUCT_ID=PR.id AND E.SPC_ID IS NULL AND E.SPD_ID IS NULL

UNION ALL

--- GETTING RECORDS OF ALL CHILDS WHOSE IMMEDIATE PARENT IS SPB ---
select A,B,C, 'SPB' from SPB B
left outer join SPC C on C.SPB_id=B.id
left outer join SPD D on D.SPB_id = B.id and D.SPC_id is null
--NO SPD JOIN HERE AS THERE IS NO DIRECT AND RELATION SHIP---

UNION ALL

SELECT A,B,C, 'SPC' FROM SPC C
left outer join SPD D on D.SPC_ID=C.id AND D.SPB_ID IS NULL 
LEFT OUTER JOIN SPE E ON E.SPC_ID=C.id AND E.SPD_ID IS NULL 

UNION ALL

SELECT A,B,C, 'SPD' FROM SPD D
LEFT OUTER JOIN SPE E ON E.SPD_ID=D.id AND E.SPC_ID IS NULL 

---

Table1(Product)        Table2(SPB)      Table3(SPC)
Product ProductName    SPB   P_ID       SPC    SP_B_ID  P_ID
P101     Pname1        B201  P101       C301            P101
P102     Pname2        B202  P103       C302    B201    P101
P103     Pname3        B203  P103       C303    B202    P103
                       B204  P101       C304    B203    P103
                                        C305    B202    P103

预期结果：

    P_ID    SP_B_ID  SP_C_ID    Imed_PAR                
    P101    B201     C302       SPB             
    P101    B204                Product                 
    P101             C301       Product  

标签： sqloracle