-- Establish a runtime environment
Create Table
(Idint,
Fatheridint,
Namevarchar (10)
)
Insert a select 1, null, 'TT'
Union all select 2, 1, 'A'
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 a function
// Subnode for retrieving words
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. ID,. fatherid,. name from a inner join @ Tree B on. fatherid = B. ID and. id not in (select ID from @ tree)
Return
End
Go
// Obtain the parent node
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. ID,. fatherid,. namevar from a inner join @ Tree B on. id = B. fatherid and. id not in (select ID from @ tree)
Return
End
-- Zookeeper
Select * From DBO. getchildren (1)
Select * From DBO. getparent (9)
Go