时间戳

首页 > 解决方案 > 对多个表的层次结构/递归查询

sql - 对多个表的层次结构/递归查询

问题描述

我们有一个要求，我们需要根据父 ID 获取完整的记录层次结构。我们尝试使用左外连接，但性能不佳，查询变得巨大。

Oracle版本：Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

说，产品是由孩子的组合组成的，比如孩子1，孩子2..孩子5。我们在这里保持的关系是：

Product can be direct parent of child1, child2, child3, child4.
child1 can be direct parent of child2, child3 only.
child2 can be direct parent of child3, child4.
child3 can be direct parent of child4.

为简单起见，我们为数据提供了 3 个表格。_Imed_Par = 子/子产品的直接父级在此处输入图像描述

Table1                 Table2           Table3
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
   Expected Result:                             
    P_ID    SP_B_ID  SP_C_ID    Imed_PAR                
    P101    B201     C302       SPB             
    P101    B204                Product                 
    P101             C301       Product     
    


 --- 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

标签： sqloraclehierarchical-data

解决方案

当不知道父子关系的层数时，可以使用递归 CTE 方法来解决问题。在此处查看详细讨论

https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2

但是在您给出的示例中，父子关系的级别似乎是固定且已知的。在这种情况下，您可以通过LEFT JOINS. 如果您对此有疑问，分享您的实际查询可能有助于我们更好地了解挑战。

推荐阅读