SQL round robin: queries SQL statements that support all data of a certain category in an infinitus data table

If you want to query all data of a certain category in a data table that supports infinitus classification

For example, a data table contains a product table that supports infinitus classification.

I want to find the following data and only find the data of "Category A" and all its subcategories.

How can I write SQL statements?

 

The following is my solution

SQL code

-- Create a data table. This can be one of your physical tables.
Declare @ temp table (
ID varchar (20 ),
ParentID varchar (20 ),
Name nvarchar (1, 100 ))

Insert into @ temp values ('A', '', 'category ')
Insert into @ temp values ('B', '', 'category B ')
Insert into @ temp values ('C', '', 'category C ')
Insert into @ temp values ('D', '', 'category D ')
Insert into @ temp values ('a1', 'A', 'classification a1 ')
Insert into @ temp values ('a2 ', 'A', 'classification A2 ')
Insert into @ temp values ('a11', 'a1', 'category a11 ')
Insert into @ temp values ('a12', 'a1', 'category a12 ')

Select * from @ temp

-- Query all data under "Category"
Declare @ $ result table (
ID varchar (20 ),
ParentID varchar (20 ),
Name nvarchar (100 ),
MyLevel int)
Declare @ Level int
Set @ Level = 0
Insert into @ $ result select *, @ Level from @ temp where ID = 'A'

While (@ rowcount> 0)
Begin
Set @ Level = @ Level + 1
Insert into @ $ result
Select a. *, @ level
From @ temp A, @ $ Result B
Where a. parentid = B. ID and mylevel = @ level-1
End
Select * From @ $ result

 

The query result is as follows:

 

The key is to set a flag level for polling to find the desired data.