如何提取层次结构，直到根从另一个表和叶存在于另一个表?使用Oracle SQL

Question

这是一个层次结构的表，直到根x

<表类="年代桌子">父子tbody><453aaaab55eeexbbbbbbbbbb td>eeeeexcccccccccccxeeexdddddddd<dd/td>d

这个问题对我来说也有点困惑，但这里有一种可能性来完成它(因为我已经得到了它)。基本上，它是用复杂的order by子句一步一步递归来得到你想要的结果。问候…

WITH
fltr AS         -- Filtering table
(
Select 'a' "PARENT",  '1'  "CHILD"  From DUAL   UNION ALL
Select 'a' "PARENT",  '12' "CHILD"  From DUAL   UNION ALL
Select 'b' "PARENT",  '3'  "CHILD"  From DUAL   UNION ALL
Select 'c' "PARENT",  '4'  "CHILD"  From DUAL   UNION ALL
Select 'd' "PARENT",  '5'  "CHILD"  From DUAL 
),
nodes AS
(
Select 'a'    "PARENT",   '45'  "CHILD"     From DUAL   UNION ALL
Select 'aaa'  "PARENT",     'aa'  "CHILD"   From DUAL   UNION ALL
Select 'x'    "PARENT",     'aaa' "CHILD"   From DUAL   UNION ALL
Select 'aa'   "PARENT",     'a'   "CHILD"   From DUAL   UNION ALL
Select 'b'    "PARENT",   '55'  "CHILD"     From DUAL    UNION ALL
Select 'ee'   "PARENT",     'e'   "CHILD"   From DUAL     UNION ALL
Select 'x'    "PARENT",     'bbb' "CHILD"   From DUAL   UNION ALL
Select 'bbb'  "PARENT",     'bb'  "CHILD"   From DUAL   UNION ALL
Select 'bb'   "PARENT",     'b'   "CHILD"   From DUAL   UNION ALL
Select 'eee'  "PARENT",     'ee'  "CHILD"   From DUAL   UNION ALL
Select 'x'    "PARENT",     'ccc' "CHILD"   From DUAL     UNION ALL
Select 'ccc'  "PARENT",     'cc'  "CHILD"   From DUAL     UNION ALL
Select 'cc'   "PARENT",     'c'   "CHILD"   From DUAL   UNION ALL
Select 'x'    "PARENT",     'eee' "CHILD"   From DUAL   UNION ALL
Select 'x'    "PARENT",     'ddd' "CHILD"   From DUAL   UNION ALL
Select 'ddd'  "PARENT",     'dd'  "CHILD"   From DUAL   UNION ALL
Select 'dd'   "PARENT",     'd'   "CHILD"   From DUAL
),
--  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --  --
end_level AS
(  
Select Distinct
f.PARENT "PARENT", 
f.CHILD "CHILD"
From  
fltr f
Left Join
nodes n ON(1=1)
Where 
n.PARENT = 'x'
),
midend AS
(
SELECT
n.PARENT,
n.CHILD
FROM
end_level e
LEFT JOIN
nodes n ON(n.CHILD = e.PARENT)
),
midroot AS
(
SELECT
n.PARENT,
n.CHILD
FROM
midend e
LEFT JOIN
nodes n ON(n.CHILD = e.PARENT)
)
SELECT DISTINCT 
PARENT,
CHILD
FROM 
(
SELECT PARENT, CHILD FROM NODES WHERE PARENT = 'x' And SubStr(CHILD, 1, 1) IN(SELECT PARENT FROM fltr) UNION ALL
SELECT PARENT, CHILD FROM midroot UNION ALL
SELECT PARENT, CHILD FROM midend UNION ALL
SELECT PARENT, CHILD FROM end_level
)
ORDER BY 
CASE 
WHEN SubStr(PARENT, 1, 1) = 'x' 
THEN LPAD(SubStr(CHILD, 1, 1), 2, SubStr(CHILD, 1, 1))
WHEN SubStr(PARENT, 1, 1) = SubStr(CHILD, 1, 1)
THEN LPAD(SubStr(CHILD, 1, 1), 3, SubStr(CHILD, 1, 1)) || To_Char(10 - Length(CHILD))
WHEN SubStr(PARENT, 1, 1) = SubStr(CHILD, 1, 1)
THEN LPAD(SubStr(CHILD, 1, 1), 4, SubStr(CHILD, 1, 1)) || CHILD
ELSE
LPAD(SubStr(PARENT, 1, 1), 5, SubStr(PARENT, 1, 1)) || CHILD
END
--  
--  R e s u l t 
--  
--      PARENT CHILD
--      ------ -----
--      x      aaa   
--      aaa    aa    
--      aa     a     
--      a      1     
--      a      12    
--      x      bbb   
--      bbb    bb    
--      bb     b     
--      b      3     
--      x      ccc   
--      ccc    cc    
--      cc     c     
--      c      4     
--      x      ddd   
--      ddd    dd    
--      dd     d     
--      d      5

它可以以同样的方式深入，唯一的限制是根的固定值'x'，但它在问题中明确地陈述了，所以…