SQL Server Common table expression (CTE) implementation recursion

Introduction to Common table expressions:

A common table expression (CTE) can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and is only valid for the duration of the query. Unlike a derived table, a common table expression (CTE) has an important advantage in being able to reference itself to create a recursive CTE. A recursive CTE is a common table expression that repeats an initial CTE to return a subset of data until the full result set is obtained.

The following first creates a table and inserts some data:

CREATE TABLE Role_cte (    Id        int not             null,    Name    nvarchar (+) not NULL,    parentid    int        Not null) INSERT INTO ROLE_CTE (id,name,parentid) Select ' 1 ', ' super admin ', ' 0 ' union select ' 2 ', ' admin a ', ' 1 ' union select ' 3 ', ' admin b ', ' 2 ' union select ' 4 ', ' member AA ', ' 2 ' union select ' 5 ', ' member Ab ', ' 2 ' union select ' 6 ', ' member Ba ', ' 3 ' union select ' 7 ', ' member BB ', ' 3 ' Unio N Select ' 8 ', ' User aaa ', ' 4 ' union select ' 9 ', ' User bba ', ' 7 '  --Creates a composite clustered index create clustered index Clu_role_cte_indexon Role_ CTE (Id,parentid) with (    Pad_index=on,    fillfactor=50,    drop_existing=off,    Statistics_ Norecompute=on) SELECT * FROM Role_cte

Finds all descendant nodes of the specified node:

Implemented using plain SQL statements:

DECLARE @level  intdeclare @node    intdeclare @ResTab Table (    node    int not null,    LV        int. NOT NULL)    set @level =0        --Indicates the initial level set @node =3            --Represents the initial node ID, which is the start of the specified node to find the insert into @ResTab                -- Insert the initial data select Id for the table variable, @level from role_cte where [email protected]while (@ @ROWCOUNT >0) begin    Set @[email Protected]+1    INSERT INTO @ResTab    the Select b.ID, @level from     @ResTab a     joins Role_cte B on A.node=b. ParentID and [email protected]    --join equals inner JOIN (inner connection) and self-connect endselect a.node,b.name,a.lv from @ResTab a left join Ro Le_cte B on A.node=b.id

The above is based on the specified node ID (3), the Lookup parent node ID (that is, field parentid) equals the specified node ID, inserts if any, and continues the loop.

Ps:[email protected] is the focus, otherwise it will go into the dead loop, the effect is to limit the insertion only once.

If you need to limit the number of loops, that is, the number of recursive layers, you only need to add a limit to the while condition. As follows:

DECLARE @level  intdeclare @node    intdeclare @num    intdeclare @ResTab Table (    node    int not NULL,    LV        int NOT null    ) set @level =0        --Represents the initial level set @node =3            --Represents the initial node ID, which is the start of the specified node to find the set @num =1        -- Specifies the recursive level, that is, the number of loops insert                into @ResTab-Inserts the initial data select Id for the table variable, @level from role_cte where [email protected]while (@@ Rowcount>0 and @level < @num) begin    Set @[email protected]+1    insert INTO @ResTab    select b.ID, @level     from @ResTab a     join Role_cte B on A.node=b.parentid and [email protected]    -join equals inner JOIN (inner connection) and self-connected en Dselect a.node,b.name,a.lv from @ResTab a LEFT join Role_cte B on a.node=b.id

Of course, if you specify the number of cycles, you can use the @ @rowcount >0 without the while judgment statement.

Using the SQL CTE implementation:

DECLARE @node    int set @node =3;with temp_cteas (    select id,name,0 LV        --Queries out "root node", that is, the specified starting node from    role_cte< C4/>where [email protected]     UNION ALL    Select B.id,b.name,a.lv+1 from     temp_cte a     join Role_cte B on a.id= B.parentid) SELECT * FROM Temp_cte

Use the CTE to control the number of levels of recursion, similar to the above. As follows:

DECLARE @node    int declare @num    intset @node =3;set @num =1;with temp_cteas (    select id,name,0 LV        -- Query out "root node", which is the specified starting node from    role_cte     where [email protected]     UNION ALL    Select B.id,b.name,a.lv+1 From     temp_cte a     joins ROLE_CTE B on A.id=b.parentid and                    a.lv< @num        --control recursion layer) SELECT * FROM Temp_cte

Finds all ancestor nodes of the specified node:

Implemented using plain SQL statements:

DECLARE @level  intdeclare @node    intdeclare @num    intdeclare @ResTab Table (    node    int not NULL,    LV        int NOT null    ) set @level =0    --Represents the initial level set @node =8            --Represents the initial node ID, which is the start of the specified node to find the set @num =2        -- Specifies the recursive hierarchy, that is, the number of cycles while (@level <[email protected] and @node are    not null)--if NULL means no parent is found. Begin        INSERT INTO @ Restab    Select @node, @level    set @[email protected]+1    select @node =parentid from     role_cte     where [email protected]endselect a.node,b.name,a.lv from @ResTab a LEFT join Role_cte B on a.node=b.id

Using the SQL CTE implementation:

DECLARE @node    int declare @num    intset @node =8;set @num =2;with temp_cteas (    select id,name,parentid,0 LV        --Query out "root node", that is, the specified starting node from    role_cte     where [email protected]     UNION ALL    Select B.id,b.name,b. Parentid,a.lv+1 from     temp_cte a     joins ROLE_CTE B on A.parentid=b.id and                    a.lv < @num        -control recursion layer) Select * FROM TEMP_CTE

SQL Server Common table expression (CTE) implementation recursion