SQL SERVER 2000 Calendar Parent-Child relational data table (binary tree) get all child nodes all parent nodes and node layer functions

---SQL SERVER 2000 Calendar Parent-child relationship tables (binary tree) get all parent nodes and node layer functions for all child nodes
---geovin Du tu ju wen
--Building a test environment
Create Table Geovindu
([ID] Int,
Fatherid Int,
[Name] Varchar (10)
)
Insert A Select 1, 0, ' China '
Union All Select 2, 1, ' Guangdong '
Union all Select 3, 1, ' Beijing '
Union all Select 4, 2, ' Shenzhen sez '
Union all Select 5, 2, ' Guangzhou '
Union all Select 6, 4, ' Lo Wu '
Union all Select 7, 4, ' Fukuda '
Union all Select 8, 7, ' Huaqiang North '
Union all Select 9, 0, ' United States '
Union all Select 10, 9, ' Washington State '
GO
--building functions

--Take Word sub-node
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ GetChildren] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [GetChildren]
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 Geovindu Where Fatherid = @ID
While @ @Rowcount > 0
Insert @Tree Select a.id, A.fatherid, a.name from Geovindu A Inner Join @Tree B to A.fatherid = b.ID and a.ID not in (Sele CT ID from @Tree)---
Return
End
GO

--Take parent node

Set ANSI_NULLS on
SET QUOTED_IDENTIFIER the
GO
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [ DBO]. [GetParent] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
drop function [dbo].[ GetParent]
GO
Create Function [dbo].[ GetParent] (@ID Int)
Returns @Tree Table (ID Int, Fatherid int, Name Varchar)
as
Begin
Insert @Tree Select ID, Fatherid, Name from Geovindu Where ID = @ID
while @ @Rowcount > 0
Insert @Tree Select a.id, A.fatherid, A.nam E from Geovindu A Inner Join @Tree B to a.ID = B.fatherid and a.ID not in (Select ID from @Tree)
Return
End

---are divided into a few
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ F_getlevel] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [F_getlevel]
GO
Create function F_getlevel (@id int) returns INT
As
Begin
DECLARE @re_str as varchar (3)
Set @re_str = ' '
DECLARE @level as int
Set @level = 1
Select @re_str = Fatherid from Geovindu where [ID] = @id
While exists (select 1 from Geovindu where [ID] = @re_str)
Begin
Select @re_str = Fatherid from Geovindu where [ID] = @re_str
Set @level = @level + 1
End
Return @level
End
Go

--Tests
SELECT * from Geovindu
GO

Select * FROM dbo. GetChildren (4)

Select * FROM dbo. GetParent (4)
GO

SELECT *, Dbo.f_getlevel ([ID]) ' Level ' from A

Drop function Dbo.f_getlevel

SQL SERVER 2000 Calendar Parent-Child relational data table (binary tree) get all child nodes all parent nodes and node layer functions