Sqlserver use CTE

1. What is CTE?

The full name of CTE is common table expression, which is translated as a common table expression. This expression is derived from a simple query and can be considered
Temporary result set defined in the execution scope of select, insert, update, delete, or create view statements. 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.

2. CTE advantages 
Using CTE can improve readability and easily maintain complex queries.

Queries can be divided into separate blocks, simple blocks, and logical generation blocks. Then, these simple blocks can be used to generate more complex temporary CTE until the final result set is generated.
3. The scope of CTE can be defined in user-defined routines (such as functions, stored procedures, triggers or views.
4. CTE syntax

[WITH <common_table_expression> [ ,...n ]]<common_table_expression>::=        expression_name [( column_name [ ,...n ] )]    AS        (CTE_query_definition)

Parameters:

Expression_name

A valid identifier of a public table expression. Expression_name must be different from any other public table expression defined in the with <common_table_expression> clause, but expression_name can be the same as the name of the base table or base view. Any reference to expression_name in a query uses a public table expression instead of a base object.

Column_name

Specify the column name in the public table expression. Duplicate names are not allowed in a CTE definition. The specified column name must match the number of columns in the cte_query_definition result set. The column name list is optional only when different names are provided for all result columns in the query definition.

Cte_query_definition

Specifies a SELECT statement whose result set is filled with a common table expression. Except that the CTE cannot define another CTE, the SELECT statement of cte_query_definition must meet the same requirements as when creating a view.

5. Define and use CTE

(1) applied to non-recursive CTE

After CTE, you must follow the select, insert, update, or delete statements that reference some or all of the CTE columns. You can also specify CTE as part of the select Definition Statement in the create view statement. Multiple CTE query definitions can be defined in non-recursive CTE. The definition must be used with one of the following collection operators: Union all, union, intersect, or except T. The CTE can reference itself or be referenced in the same
The pre-defined CTE in the clause. Forward references are not allowed. Multiple with clauses cannot be specified in a CTE. For example, if cte_query_definition contains a subquery, The subquery cannot include a nested with clause that defines another CTE. The following clause cannot be used in cte_query_definition:

Compute or compute

Orderby (unless top clause is specified)

Into

Option clause with query prompt

Forxml

Forbrowse

If you use CTE in a statement that is part of a batch, the statement before it must end with a semicolon.

You can use a query that references CTE to define a cursor.

You can reference tables on the remote server in CTE.

During CTE execution, any prompts that reference CTE may conflict with other prompts found when the CTE accesses its basic table, this type of conflict is the same as that of the view prompt in the reference query. In this case, the query returns an error.

(2) Define and use recursive CTE

A. Define recursive CTE

The positioning point member must be used with one of the following collection operators: Union all, union, intersect, or except T. Only the Union all operator can be used when the last position Member and the first recursive member are combined and multiple recursive members are combined. The recursive CTE definition must contain at least two CTE query definitions, one positioning point member and one recursive member. Multiple positioning point Members and recursive members can be defined, but all positioning Point member query definitions must be placed before the first recursive member definition. All
The CTE query definitions are all positioning point members, except when they reference the CTE itself. The number of columns in the anchor and recursive members must be the same. The data type of the column in the recursive member must be the same as that of the corresponding column in the position member. The from clause of a recursive member can only reference cteexpression_name once. In the recursive member's
The following items cannot appear in cte_query_definition:

Select distinct

Groupby

Having

Scalar Aggregation

Top

Left, right, and outer join (inner join is allowed)

Subquery

Apply to the prompt of recursive reference to CTE in cte_query_definition.

B. Recursive CTE

All columns returned by recursive CTE can be empty regardless of whether the columns returned by the involved SELECT statement are null.

If the recursive CTE combination is incorrect, infinite loops may occur. For example, if the recursive member query defines that the same value is returned for the parent and child columns, an infinite loop will occur. You can use the maxrecursion prompt and a value between 0 and 32,767 in the option clause of an insert, update, delete, or select statement to limit the recursive series allowed by a specific statement, to prevent infinite loops. In this way, you can control the execution of statements before solving the code issue that generates loops. The default value for the server is
100. If 0 is specified, there is no limit. Each statement can only specify one maxrecursion value.

Data cannot be updated using views that contain recursive common table expressions.

You can use CTE to define the cursor in the query. Recursive CTE only allows quick forward and static (snapshot) cursors. If other types of cursors are specified in recursive CTE, the type is converted to static cursors.

You can reference tables on the remote server in CTE. If the remote server is referenced in the recursive Member of the CTE, a dummy offline is created for each remote table so that the tables can be accessed locally repeatedly.

If multiple cte_query_definition statements are defined, these query definitions must be joined using one of the following collection operators: Union all, union, except T, or intersect.