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

//取父节点

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER Function [dbo].[GetParent](@ID Int)

Returns @Tree Table (ID Int, fatherID Int, NameVar Varchar(10))

As

Begin

Insert @Tree Select ID, fatherID, NameVar From A Where ID = @ID

While @@Rowcount > 0

Insert @Tree Select A.ID, A.fatherID, A.NameVar From A A Inner Join @Tree B On A.ID = B.fatherID And A.ID Not In (Select ID From @Tree)

Return

End

--測試

Select * From dbo.GetChildren(1)

Select * From dbo.GetParent(9)

GO