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 a temporary result set that is 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 valid only for the duration of the query. Unlike a derived table, a CTE can be referenced and can be referenced multiple times in the same query.

Use the Test4 table above for example:

With TEST_CTE

As

(

SELECT * FROM Test4

)

This sentence creates a result set of the SELECT * from test4 named Test_cte. Because it is not stored as an object and is valid only for the duration of the query, the CTE and query statements need to 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 following is the CTE usage for the specified column:

With TEST_CTE (ID)

As

(

Select ID from test4

)

The columns in the definition need to correspond to the columns inside the statement, see the red font.

A CTE can be used to create recursive queries.

To 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, ' Sub Class 111 ', 11

UNION ALL

Select 22, ' Subclass 22 ', 2

UNION ALL

Select 222, ' Sub Class 222 ', 22

Results:

ID Name ParentID

1 Parent Class 1 0

2 Parent Class 2 0

3 Parent Class 3 0

11 Sub Class 11 1

12 Sub Class 12 1

111 Sub Class 111 11

22 Sub Class 22 2

222 Sub Class 222 22

Create a recursive query using a CTE to get the subclass of the parent Class 1 and all of its subclasses and subclasses ... ：

With Test_recursion (Id,name,parentid,[level])

As

(

Select id,name,parentid,0 from test5 where ID =1--no reference to the CTE itself must be placed above the first recursive row

Union all--No reference to the CTE itself and the first recursive line must be 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

Results:

ID Name ParentID level

1 Parent Class 1 0 0

11 Sub Class 11 1 1

12 Sub Class 12 1 1

111 Sub Class 111 11 2