SQL does not use recursion, but uses a flat history for the region level.

Region table structure

Id parentid name

1 0 A1

2 1 A1-1

......

/* Temporary table */

Create Table # t_dq (
Id integer null,
Parentid integer null,
Name varchar (50) null,
Grade integer null,/* level */
State varchar (10) null/* 1: not processed; 2: processed */
)

/* SQL indicates the query region, including the sub-region */

Insert into # t_dq (ID, parentid, name, grade, state)
Select ID, parentid, name, 1, '1' from t_dq where parentid = @ area/* region to be queried */

/* Insert sub-regions cyclically */
Select @ ID = min (ID) from # t_dq
While @ ID is not null
Begin
Select @ grade = Grade from # t_dq where id = @ ID
Insert into # t_dq (ID, parentid, name, grade, state)
Select ID, parentid, name, @ grade + 1, '1' from # t_dq1 where parentid = @ ID
Update # t_dq set state = '2' where id = @ ID
Select @ ID = min (ID) from # t_dq where State <> '2'
End

 

Select * from # t_dq

 

Hope to help you!