--建立測試環境
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
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。