CTE statement structure and CTE recursive query in SQL SERVER

CTE statement structure and CTE recursive query in SQL SERVER

CTE statement Structure

A common table expression (CTE) can be considered as a temporary result set defined within the execution range of a single SELECT, INSERT, UPDATE, DELETE, or create view statement. CTE is similar to a derived table. It is not stored as an object and is only valid during the query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query.

For the test4 table, see apply operator in SQL SERVER.

The preceding test4 table is used as an example:

WITH TEST_CTE
AS
(
Select * from test4
)

This statement creates a select * from test4 result set named TEST_CTE. Because it is not stored as an object and only valid during the query, the CTE and query statements must be executed together:

WITH TEST_CTE
AS
(
Select * from test4
)
Select * from TEST_CTE

The result set is the same as the select * from test4 result set machine.

The CTE of the specified column is used as follows:

WITH TEST_CTE (Id)
AS
(
SelectIdFrom test4
)

The columns in the definition must correspond to the columns in the statement. See the red font.

CTE can be used to create recursive queries.

Create a test table and insert data:

Create table test5
(
Id int,
Name varchar (50 ),
Parentid int
)
Insert into test5 (id, name, parentid)
Select 1, 'parent class 1', 0
Union all
Select 2, 'parent class 2', 0
Union all
Select 3, 'parent class 3', 0
Union all
Select 11, 'subclass 11', 1
Union all
Select 12, 'subclass 12', 1
Union all
Select 111, 'subclass 111 ', 11
Union all
Select 22, 'subclass 22', 2
Union all
Select 222, 'subclass 222 ', 22

Result:

Id name parentid
1 parent class 1 0
2 parent class 2 0
3 parent class 3 0
11 subclass 11 1
12 subclass 12 1
111 sub-classes 111 11
22 subclass 22 2
222 sub-classes 222 22

Use CTE to create a recursive query and obtain the sub-classes of parent class 1 and all its sub-classes and sub-classes ...:

With Test_Recursion (id, name, parentid, [level])
AS
(
Select id, name, parentid, 0 from test5 where id = 1 -- the CTE itself (Test_Recursion) is not referenced and must be placed on the first recursive row.
Union all -- the statement that does not reference the CTE itself and the first recursive row must use UNION ALL
Select a. id, a. name, a. parentid, B. [level] + 1 from test5 as a join Test_Recursion as B on a. parentid = B. id -- Recursive row
)
Select * from Test_Recursion

Result:

Id name parentid level
1 parent class 1 0 0
11 subclass 11 1 1 1
12 subclass 12 1 1 1
111 sub-classes 111 11 2