T-SQL query Advanced: An Explanation common table expressions (CTE)

Brief introduction

For select query statements, typically, in order to make T-SQL code more concise and readable, Referencing a different result set in a query is decomposed by a view rather than a subquery. However, the view is in the database as a system object, and it is a luxury to use a view for a result set that only needs to be used once in a stored procedure or user-defined function.

Common table Expressions (Common table Expression) are an attribute introduced after the SQL SERVER 2005 version. A CTE can be thought of as a temporary result set that can be referenced more than once in the next Select,insert,update,delete,merge statement. Use common expressions to make statements clear and concise.

In addition, according to Microsoft's description of the CTE benefits, it can be summed up to four points:

You can define a recursive common table expression (CTE)

A CTE makes it more concise when you don't need to refer a result set as a view to multiple places

A GROUP by statement can directly act on a scalar column derived from a subquery

Common table Expressions (CTE) can be referenced more than once in a single statement

Definition of common table expression (CTE)

The definition of a common expression is simple and contains only three parts:

The name of the common table expression (after with)

Name of the column involved (optional)

A SELECT statement (immediately after as)

Prototypes in MSDN:

With Expression_name [(column_name [,... N])] as 

 

A common table (CTE) expression can be divided into recursive common table expressions and non-recursive common table expressions, whether recursive or not.

Non-recursive common table expression (CTE)

A non-recursive common table expression (CTE) is a query result that returns only one result set for external query calls. does not call its own CTE in the statement that it defines

Non-recursive common table expressions (CTE) are used in the same way as views and subqueries

For example, a simple recursive common-table expression:

Of course, one of the benefits of common table expressions is that you can refer to them multiple times in the Next statement: