sql如何进行父子关系遍历

sql管理工具

计算机

先建立需要测试的表格，及插入测试数据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

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

创建相应的遍历函数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

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

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