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

2、执行该语句，得到相应的数据库表格和数据

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

4、现在进行测试下函数的执行效果Select * From dbo.GetChildren(1)GO

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