Daily SQL (queries all subnodes and Their Function exercises)

------ TB
Create Table Tb (ID varchar (3), PID varchar (3), name varchar (10 ))

Insert into TB values ('001', null, 'henan province ')
Insert into TB values ('002 ', '001', 'luoyang ')
Insert into TB values ('003 ', '001', 'zhengzhou ')
Insert into TB values ('004 ', '002', 'old town ')
Insert into TB values ('005 ', '003', 'zone 2 and seven ')
Insert into TB values ('006 ', '003', 'jinshui ')
Insert into TB values ('007 ', '003', 'huiji Region ')
Insert into TB values ('008 ', '007', 'a Zhen ')
Insert into TB values ('009', '007 ',' B Zhen ')
Insert into TB values ('010 ', '007', 'c Zhen ')

-- Query the f_cid function of a specified node and all its subnodes
Create Function f_cid (@ ID varchar (3) returns @ t_level table (ID varchar (3), Level INT)
As
Begin
Declare @ level int
Set @ level = 1
Insert into @ t_level select @ ID, @ level
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert into @ t_level select a. ID, @ level
From tb a, @ t_level B
Where a. PID = B. ID and B. Level = @ level-1
End
Return
End
Go

Select * from TB
-- Call function query 001 (Henan) and all its subnodes
Select a. * from tb a, f_cid ('001') B where a. ID = B. ID order by A. ID
-- Call the function to query 002 and all its subnodes
Select a. * from tb a, f_cid ('002 ') B where a. ID = B. ID order by A. ID
-- Call the function to query 003 and all its subnodes
Select a. * from tb a, f_cid ('003 ') B where a. ID = B. ID order by A. ID

--------- The result is displayed as follows:

Id PID name
------------------
001 null Henan Province
002 001 Luoyang City
003 001 Zhengzhou City
004 002 Old City
005 003 Zone 2 and 7
006 003 jinshui District
007 003 huiji District
008 007 a town
009 007 B town
010 007 C town

(10 rows affected)

Id PID name
------------------
001 null Henan Province
002 001 Luoyang City
003 001 Zhengzhou City
004 002 Old City
005 003 Zone 2 and 7
006 003 jinshui District
007 003 huiji District
008 007 a town
009 007 B town
010 007 C town

(10 rows affected)

Id PID name
------------------
002 001 Luoyang City
004 002 Old City

(2 rows affected)

Id PID name
------------------
003 001 Zhengzhou City
005 003 Zone 2 and 7
006 003 jinshui District
007 003 huiji District
008 007 a town
009 007 B town
010 007 C town

(7 rows affected)
 

 

-- Destroy
Drop table TB
Drop function f_cid