"Go" SQL: detailed recursive CTE, non-recursive CTE usage

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

1. The name of the common table expression (after with)
2. The name of the column involved (optional)
3. A SELECT statement (immediately after as)

Prototypes in MSDN:

Common table (CTE) expressions can be categorized as recursive common table expressions and non-recursive common table expressions, depending on whether recursion is recursive.

non-recursive common table expressions (CTE)

A non-recursive common table expression (CTE) 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 (CTE) are used in a way that is consistent with views and subqueries

For example, a simple non-recursive common table expression:

Of course, one of the benefits of a common table expression is that it can be referenced more than once in the next statement:

I have always emphasized that "in the next statement" means that only the next one is used:

Since the CTE can only be used in the next statement, you can define multiple, separated by commas, when you need to reference multiple CTE in the Next statement:

Recursive common table expressions (CTE)

Recursive common table expressions are much like derived tables (Derived Tables), Refers to a CTE that invokes itself in a statement within a CTE. Unlike derived tables, a CTE can be derived recursively multiple times in a definition. For the concept of recursion, a function or procedure calls itself directly or indirectly, a simple concept diagram of recursion as follows:

A typical example of recursive implementation in C is the Fibonacci sequence:

Long fib (int n) {      if (n==0) return 0; if (n==1) return 1;     

The above C language code can see that to form a recursive function, two parts are required. The first part is the base part, which returns a fixed value that tells the program when to start recursion. The second part is the loop part, which is a function or process that calls itself recursively or indirectly.

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:

For example: In Adventurework, I want to know the level at which each employee is located, 0 is the highest

Such complex queries have become so elegant and concise through recursive CTE. This is also the most powerful place for the CTE.

Of course, the more powerful the power, the need to be constrained. If used improperly, recursive CTE may appear infinite recursion. server resources for SQL Server. Therefore, SQL Server has the option option to set a maximum number of recursion times:

Or the above statement, which limits the number of recursion:

Messages that are prompted:

This maximum recursion frequency is often based on the specific business that the data represents, such as the assumption that the company hierarchy is at most 2 levels.

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.

Source Address: http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html

"Go" SQL: detailed recursive CTE, non-recursive CTE usage