递归SQL查询以获取父项下的子项 - SQL Server 2008

I'm trying to write query for get the children under the parent id from the table #Temp. Please find the below table #Temp

我正在尝试编写查询以从表#Temp中获取父ID下的子项。请找到下表#Temp

Note: Parent value is NULL then corresponding child value is the L1 and need to find levels from L1 to L5.

注意:父值为NULL,然后相应的子值为L1,需要查找从L1到L5的级别。

child | Parent
  c1      p1
  p1     NULL
  c2      p2
  p2      p3
  p4      p3
  p3     NULL

I'm looking for the below output

我正在寻找以下输出

 L1 | L2 | L3 | L4 | L5
 p1   c1  NULL NULL NULL
 p3   p2   c2  NULL NULL
 p3   p4  NULL NULL NULL

I have tried with this query

我试过这个查询

SELECT L1.child 'L1', 
       L2.child 'L2', 
       L3.child 'L3', 
       L4.child 'L4', 
       L5.child 'L5' 
FROM   #temp L1 
       INNER JOIN #temp L2 
               ON L2.parent = L1.child 
       INNER JOIN #temp L3 
               ON L3.parent = L2.child 
       INNER JOIN #temp L4 
               ON L4.parent = L3.child 
       INNER JOIN #temp L5 
               ON L5.parent = L4.child 
WHERE  L1.parent IS NULL 

But I'm not getting expected output. Please advice. Thanks in advance!

但我没有得到预期的输出。请指教。提前致谢!

1 个解决方案

#1

change the innerjoin to Left join:

将innerjoin更改为Left join:

Select 
    L1.child 'L1', L2.child 'L2', L3.child 'L3', L4.child 'L4', L5.child 'L5'
from 
    #Temp L1
LEFT join 
    #Temp L2 ON L2.Parent = L1.child
LEFT join 
    #Temp L3 ON L3.Parent= L2.child
LEFT join 
    #Temp L4 ON L4.Parent= L3.child
LEFT join 
    #Temp L5 ON L5.Parent= L4.child
where 
    L1.Parent is null