The third day of SQL Learning -- SQL for recursive query of CTE (public expression)

Recursive queries using CTE (common table expressions) ---- SQL Server 2005 and later versions

A common table expression (CTE) has an important advantage: It can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained.

When a query references recursive CTE, it is called recursive query. Recursive queries are usually used to return layered data, such as displaying an employee or item list scheme in an organizational chart (one or more components of the parent product, and those components may have child components, or other parent-level product components.

Recursive CTE can greatly simplify the Code required to run recursive queries in SELECT, INSERT, UPDATE, DELETE, or create view statements. In earlier versions of SQL Server, recursive queries usually require temporary tables, cursors, and logic to control recursive step flows.

The basic syntax structure of CTE is as follows:
Copy codeThe Code is as follows:
WITH expression_name [(column_name [,... n])]

AS

(CTE_query_definition)

-- The column name list is optional only when different names are provided for all result columns in the query definition.

-- The statement for running CTE is:

SELECT <column_list> FROM expression_name;

Pay attention to the following points when using CTE:

The CTE must be followed by the SQL statements that use the CTE (such as select, insert, and update). Otherwise, the CTE will become invalid. The following SQL statement cannot use CTE normally:
Copy codeThe Code is as follows:
With
Cr
(
Select * from table name where Condition
)
-- Select * from person. CountryRegion -- if this sentence is added, cr will return an error.
Select * from cr

2. The CTE can be followed by other CTE, but only one with can be used. Multiple CTE are separated by commas (,), as shown in the following SQL statement:
Copy codeThe Code is as follows:
With
Cte1
(
Select * from table1 where name like 'test %'
),
Cte2
(
Select * from table2 where id> 20
),
Cte3
(
Select * from table3 where price <100
)
Select a. * from cte1 a, cte2 B, cte3 c where a. id = B. id and a. id = c. id

3. if the expression name of the CTE is the same as that of a data table or view, the SQL statement followed by the CTE still uses the CTE. Of course, the following SQL statement uses the data table or view.

4. The CTE can reference itself, or reference the pre-defined CTE in the same WITH clause.

5. the following clause cannot be used in CTE_query_definition:
Copy codeThe Code is as follows:
COMPUTE or COMPUTE
Order by (unless TOP clause is specified)
INTO
OPTION clause with query prompt
FOR XML
FOR BROWSE

6. If you use CTE in a statement that is part of the batch processing, the statement before it must end with a semicolon, as shown in the following SQL:
Copy codeThe Code is as follows:
Declare @ s nvarchar (3)
Set @ s = 'test % '; -- add points required
With
T_tree
(
Select * from table where field like @ s
)
Select * from t_tree

------------------------------------ Operation ------------------------------------

The above may be a bit too long for with as. Next, let's start with the question:

Create a table first (Co_ItemNameSet) as follows ):
Copy codeThe Code is as follows:
Create table [dbo]. [Co_ItemNameSet] (
[ItemId] [int] NULL,
[ParentItemId] [int] NULL,
[ItemName] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

Insert data:
Copy codeThe Code is as follows:
-- Insert data to the table
Insert into dbo. Co_ItemNameSet values (2, 0, 'administrative cost ')
Insert into dbo. Co_ItemNameSet values (3, 0, 'sales cost ')
Insert into dbo. Co_ItemNameSet values (4,0, 'Financial Bill ')
Insert into dbo. Co_ItemNameSet values (5, 0, 'production cost ')
Insert into dbo. Co_ItemNameSet values (35, 5, 'docker ')
Insert into dbo. Co_ItemNameSet values (, 'artificial ')
Insert into dbo. Co_ItemNameSet values (, 'manufacturing cost ')
Insert into dbo. Co_ItemNameSet values (38,35, 'raw source ')
Insert into dbo. Co_ItemNameSet values (39,35, 'main docker ')
Insert into dbo. Co_ItemNameSet values (40, 35, 'auxiliary material interval ')
Insert into dbo. Co_ItemNameSet values (, 'payroll ')
Insert into dbo. Co_ItemNameSet values (, 'welfare ')
Insert into dbo. Co_ItemNameSet values (, 'administrative expense subitem ')
Insert into dbo. Co_ItemNameSet values (, 'subitem for managing subitem s ')

Query the inserted data:
Copy codeThe Code is as follows:
-- Query data
Select * from Co_ItemNameSet

Result chart:

The question requirement is:Query ItemId = 2 and subnodes, that is, management fee and information of all its subordinate nodes.

Operation 1: First, let's look at the SQL statements without the CTE recursive operation as follows (it is very troublesome to create two tables for data storage and judgment ):
Copy codeThe Code is as follows:
Declare @ I int
Select @ I = 2;

Create table # tem (
[ItemId] [INT] not null,
[Level] INT
);

Create table # list (
[ItemId] [INT] not null,
[ParentItemId] [INT] not null default (0 )),
[ItemName] [nvarchar] (100) not null default (''),
[Level] int
);

Insert INTO # tem ([ItemId], [level])
Select ItemId, 1
From Co_ItemNameSet
Where itemid = @ I

Insert into # list ([ItemId], [ParentItemId], [ItemName], [level])
Select ItemId, ParentItemId, ItemName, 1
From Co_ItemNameSet
Where itemid = @ I

Declare @ level int
Select @ level = 1
Declare @ current INT
Select @ current = 0

While (@ level> 0)
Begin
Select @ current = ItemId
From # tem
Where [level] = @ level
If @ ROWCOUNT> 0
Begin


Delete from # tem
Where [level] = @ level and ItemId = @ current

Insert into # tem ([ItemId], [level])
Select [ItemId], @ level + 1
From Co_ItemNameSet
Where ParentItemId = @ current

Insert into # list ([ItemId], [ParentItemId], [ItemName], [level])
Select [ItemId], [ParentItemId], [ItemName], @ level + 1
From Co_ItemNameSet
Where ParentItemId = @ current
If @ rowcount> 0
Begin
Select @ level = @ level + 1
End
End
Else
Begin
Select @ level = @ level-1
End
End

Select * from # list
Drop table # tem
Drop table # list

Result chart:

Operation 2: the SQL statement that uses CTE recursion is as follows:
Copy codeThe Code is as follows:
DECLARE @ I INT
SELECT @ I = 2;
WITH Co_ItemNameSet_CTE (ItemId, ParentItemId, ItemName, [Level])
AS
(
SELECT ItemId, ParentItemId, ItemName, 1 AS [Level]
FROM Co_ItemNameSet
WHERE itemid = @ I
UNION ALL
SELECT c. ItemId, c. ParentItemId, c. ItemName, [Level] + 1
FROM Co_ItemNameSet c inner join Co_ItemNameSet_CTE ct
ON c. ParentItemId = ct. ItemId
)
SELECT * FROM Co_ItemNameSet_CTE

Result chart:

--------------------------- Analysis (view the MSDN analysis )----------------------------

It mainly analyzes recursive operations using CTE:

Recursive CTE consists of the following three elements:

Routine call.

The first call to recursive CTE includes one or more CTE_query_definitions connected by the union all, UNION, except t, or INTERSECT operators. These query definitions form a benchmark result set of the CTE structure, so they are called "positioning point members ".

CTE_query_definitions is considered as positioning point members unless they reference the CTE itself. ALL positioning Point member query definitions must be placed before the first recursive member definition, and the union all operator must be used to join the last positioning point member and the first recursive member.

Recursive call of the routine.

Recursive calls include one or more CTE_query_definitions connected by the union all operator that references the CTE itself (the statement block in ). These query definitions are called recursive members ".

Terminate the check.

The termination check is implicit. recursion stops when no rows are returned in a call.

The recursive CTE structure must contain at least one positioning point member and one recursive member. The following pseudo code shows a simple recursive CTE component that contains an anchor point member and a recursive member.
Copy codeThe Code is as follows:
WITH cte_name (column_name [,... n])
AS
(
CTE_query_definition -- positioning Point member
UNION ALL
CTE_query_definition -- Recursive member.
)

Now let's take a look at the Recursive Execution Process:

Split the CTE expression into positioning point Members and recursive members.

Run the positioning point member to create the first call or benchmark result set (T0 ).

Run recursive members and use Ti as the input and Ti + 1 as the output.

Repeat Step 3 until an empty set is returned.

Returned result set. This is the result of executing union all on T0 to Tn.