递归SQL取亲子关系

 WITH TableTemp                                                                           
 (ItemSystemID,ParentID,ParentType,ChildID,ChildType,OrderNo,Levels)                    
 AS    (                                                                                  
 SELECT ItemSystemID,ParentID,ParentType,ChildID,ChildType,OrderNo,0                    
 FROM T_ItemSystemNode                                                             
WHERE ChildID =@ItemID                                                              
 UNION ALL                                                                                
 SELECT T.ItemSystemID,T.ParentID,T.ParentType,T.ChildID,T.ChildType,T.OrderNo,Levels+1 
 FROM T_ItemSystemNode T                                                           
 INNER JOIN TableTemp TT                                                                  
 ON T.ParentID = TT.ChildID                                                               
 WHERE T.ItemSystemID=@ItemSystemID                                                   
 AND T.ChildType = 0                                                                      
 )