SQL得到某節點下的所有節點

--建立測試環境
Create Table Tree
(Parent Int,
Child Int)
--插入數據
Insert Tree Select 1, 2
Union All Select 2, 3
Union All Select 3, 4
Union All Select 6, 7
Union All Select 7, 8
Union All Select 8, 9
Go
--建立函數
Create Function GetChild(@Parent Int)
Returns @Child Table(Parent Int,Child Int)
As
Begin
Insert @Child Select * From Tree Where Parent=@Parent
While @@ROWCOUNT>0
Insert @Child Select B.* From @Child A Inner Join Tree B On A.Child=B.Parent Where B.Parent Not In (Select Distinct Parent From @Child)
Return
End
GO
--測試
Select Child From dbo.GetChild(1)
Select Child From dbo.GetChild(6)
Select Child From dbo.GetChild(2)
GO
--刪除測試環境
Drop Table Tree
Drop Function GetChild
Go
--結果
/*
Child
2
3
4

Child
7
8
9

Child
3
4
*/