sql如何进行父子关系遍历

1、先建立需要测试的表格，及插入测试数据Create 哌囿亡噱Table A(IDInt,fatherIDInt,NameVarchar(10))Insert A Select 1, NULL, 'tt'Union All Select 2, 1, 'aa'Union All Select 3, 1, 'bb'Union All Select 4, 2, 'cc'Union All Select 5, 2, 'gg'Union All Select 6, 4, 'yy'Union All Select 7, 4, 'jj'Union All Select 8, 7, 'll'Union All Select 9, NULL, 'uu'Union All Select 10, 9, 'oo'GO

3、创建相应的遍历函数Create Function GetChildren(@ID Int拘七呷憎)Returns @Tree Table (ID Int, fatherID Int, Na罪焐芡拂me Varchar(10))AsBeginInsert @Tree Select ID, fatherID, Name From A Where fatherID = @IDWhile @@Rowcount > 0Insert @Tree Select A.ID, A.fatherID, A.Name From A A Inner Join @Tree B On A.fatherID = B.ID And A.ID Not In (Select ID From @Tree)ReturnEndGO

5、删除测试Drop Table ADrop Function GetChildren--結果/*IDfatherIDName21aa31bb42cc52gg64yy74jj87ll