SQL 遍历父子关系表(二叉树)获得所有子节点 所有父节点(转)
--建立測試環境
Create
Table A
(ID
Int,
fatherID
Int,
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
Create Function
[dbo].[GetParent](@ID Int)
Returns
@Tree Table (ID Int, fatherID Int, Name Varchar(10))
As
Begin
Insert
@Tree Select ID, fatherID, Name From A Where ID = @ID
While
@@Rowcount > 0
Insert
@Tree Select A.ID, A.fatherID, A.Name 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
posted on 2014-05-08 09:22 TouTou9055 阅读(832) 评论(0) 编辑 收藏 举报