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, Name 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