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))
As
Begin
Insert @Tree Select ID, fatherID, Name From A Where fatherID = @ID
While @@Rowcount > 0
Insert @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)
Return
End
GO
--測試
Select * From dbo.GetChildren(1)
GO
--刪除測試環境
Drop Table A
Drop Function GetChildren
--結果
/*
IDfatherIDName
21aa
31bb
42cc
52gg
64yy
74jj
87ll
*/