Several code methods for implementing recursive query in SQL database

SQLDatabaseSeveral code methods for implementing recursive queries
Table Structure

ProductCategory

CategoryID, Level, ParentCategoryID

Data

1, 1,-1

2, 1,-1

3, 2, 1

4, 3, 3

5, 2, 2

6, 4, 5

T-SQL

WITH CategoryTemp (CategoryID, ParentCategoryID) -- A temporary table is used to save the retrieved Category.

(

SELECT CategoryID, ParentCategoryID FROM ProductCategory WHERE ParentCategoryID <= 0 -- check all the first layer as the initial data. You need to check the N layers under the nth layer or which ParentCategoryID, assign the ParentCategoryID to the relevant value.

Union all -- Query N layers

SELECT pc. CategoryID, ParentCategoryID FROM ProductCategory pc

Left join CategoryTemp ct ON pc. ParentCategoryID = ct. CategoryID

WHERE ParentCategoryID> 0 -- because the first layer has been found, the first layer is filtered out.

)

SELECT CategoryID, ParentCategoryID FROM CategoryTemp

Result

1,-1

2,-1

3, 1

4, 3

5, 2

6, 5

If ParentCategoryID is assigned to 2, the result is

5, 2

6, 5

Instance

Is the Department name Superior ID?
1 y Department 0 1
31 Department y 1 1 1
32 n Zhang San 31
33 n Li 2 31
34 y Department 2 31
35 n Wang Wu 34
35 y department 3 34
36 n small 3 35
I want to contact all persons with the ID of 35 lower-level personnel, including all persons in lower-level departments.

-- Create a query function
Create function f_id (
@ Id int -- id to be queried
) Returns @ re table (id int, level int)
As
Begin
Declare @ l int
Set @ l = 0
Insert @ re select id, @ l
From table
Where parent id = @ id
While @ rowcount> 0
Begin
Set @ l = @ l + 1
Insert @ re select a. id, @ l
From table a join @ re B on a. Parent id = B. id and B. level = @ L-1
End
Return
End
Go

-- Call a function for query
Select a. * from Table a join f_id (35) B on a. id = B. id

Joint Query

-- Test Data
Create table (ID int, whether it is department char (1), department name varchar (10), parent ID int)
Insert TABLE select 1, 'y', 'department 0', 1
Union all select 31, 'y', 'department 1', 1
Union all select 32, 'n', 'zhang san', 31
Union all select 33, 'n', 'Lee 2', 31
Union all select 34, 'y', 'department 2', 31
Union all select 35, 'n', 'wang wu', 34
Union all select 35, 'y', 'department 3', 34
Union all select 36, 'n', 'small Three', 35
Go

-- Create a query function
Create function f_id (
@ Id int -- id to be queried
) Returns @ re table (id int, level int)
As
Begin
Declare @ l int
Set @ l = 0
Insert @ re select id, @ l
From table
Where parent id = @ id
While @ rowcount> 0
Begin
Set @ l = @ l + 1
Insert @ re select a. id, @ l
From table a join @ re B on a. Parent id = B. id and B. level = @ L-1
End
Return
End
Go

-- Call a function for query
Select a. * from Table a join f_id (35) B on a. id = B. id
Go

-- Delete test
Drop table
Drop function f_id

/* -- Test Result

Is the Department name Superior ID?
-------------------------------------
36 n small 3 35

(The number of affected rows is 1)