A tree structure table recursively obtains a node and all its child nodes.

-- Instance Database
Create Table [bt_area] ([ID] int identity (1, 1), [pid] int, name varchar (20 ))
Insert [bt_area] Select 0, 'China'
Union all select 0, 'America'
Union all select 0, 'Canada'
Union all select 1, 'beijing'
Union all select 1, 'shanghai'
Union all select 1, 'shandong'
Union all select 6, 'jining'
Union all select 7, 'micromountain'
Union all select 6, 'jinan'
Union all select 6, 'dezhou'
Union all select 2, 'newyork'
Union all select 2, 'san Francisco'
Go
-- Obtain database functions of itself and its descendants
Create Function [DBO]. [getselfandchildid] (@ pid int)
Returns @ Re table (id int)
As
Begin
Insert into @ Re select ID from bt_area where pid = @ PID
While @ rowcount> 0
Begin
Insert into @ Re select a. ID
From bt_area a inner join @ Re B on A. PID = B. ID
Where a. id not in (select ID from @ Re)
End
Insert into @ Re values (@ PID)
Return
End
-- Execute database functions
Select * From bt_area where ID in (select * From getselfandchildid (1 ))