看个实例:应该属于BOM多级展开的范畴吧--建立測試環境 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 */
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
*/