sql-Common table Expressions (CTE)

definitionA common table expression (CTE), which is a temporary named result set that is defined in a query, is used in the FROM clause. Each CTE is defined only once (but can be referenced any time within its scope) and will survive for the duration of the query. You can use a CTE to perform recursive operations. The syntax created is: with <name of cte> (<column names>) as (<actual query>) SELECT * from <name of your cte> using public Benefits of a common table expression:

• Recursive common table Expressions (CTE) can be defined
• A CTE can make it more concise when you don't need to reference the result set as a view by multiple places
• A GROUP by statement can directly act on a scalar column derived from a subquery
• Common table expressions can be referenced more than once in a single statement

non-recursive common table expressions

A non-recursive common table expression is a query result that returns only one result set for an external query call. Does not invoke its own CTE in the statement it defines. Non-recursive common table expressions are used in a way that is consistent with views and sub-queries.

For example, a simple non-recursive common table expression

recursive common table expressions

A recursive common table expression refers to a CTE that calls itself in a statement within a CTE.

For recursive common expressions, the implementation principle is the same, and you need to define two parts in the statement:

• Basic statement
• Recursive statements

The two parts of SQL are returned through the Union ALL connection result set:
Like what:

First build a table column table as follows, column ID, column name, column of the parent column.

　　　

Now using the CTE to query each of its columns is the code of the first layer of the following columns:

With Col_cte (Id,name,parentid,tlevel) as (    --Basic statement    SELECT id,name,parentid,0 as Tlevel from COL    WHERE ParentID = 0    UNION All    --recursive statement    SELECT c.id,c.name,c.parentid,ce.tlevel+1 as Tlevel from COL as C     INNER JOI N Col_cte as CE--recursive call on    C.parentid = CE. ID) SELECT * from Col_cte

The output results are as follows:

　　

0 represents the top-level column. 1 is the Level 1 column. The syntax is very elegant. Just a SELECT * fron col_cte. This is where the CTE is strong, but it has to be constrained, otherwise if unrestricted recursion can consume a lot of system resources. The maximum number of recursion can also be limited by option (maxrecursion N).

If you change the query syntax above to:

With Col_cte (Id,name,parentid,tlevel) as (    --Basic statement    SELECT id,name,parentid,0 as Tlevel from COL    WHERE ParentID = 0    UNION All    --recursive statement    SELECT c.id,c.name,c.parentid,ce.tlevel+1 as Tlevel from COL as C     INNER JOI N col_cte as CE on     c.parentid = CE. ID) SELECT * from Col_cteoption (maxrecursion 2)--Specify a maximum recursion count of 2

Attention points when using

Sometimes it is advisable to add '; ' before starting with The statement before the end of the symbol. Because with his meaning in other places, make a distinction.

Summary

The CTE is a very elegant existence. The greatest benefit of the CTE is the increased readability of code, which is one of the essential qualities of good code. Recursive CTE makes it easier and more enjoyable to implement complex queries in an elegant and concise manner.

sql-Common table Expressions (CTE)