Recursive query using a common table expression

Microsoft introduced CTE (CommonTableExpression) from SQL2005 to strengthen T-SQL. A common table expression (CTE) has an important advantage: It can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained. When a query references

Microsoft introduced CTE (Common Table Expression) from SQL2005 to strengthen T-SQL. A common table expression (CTE) has an important advantage: It can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained. When a query references

Microsoft introduced CTE (Common Table Expression) from SQL2005 to strengthen T-SQL. A common table expression (CTE) has an important advantage: It can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained. When a query references recursive CTE, it is called recursive query. Recursive queries are usually used to return layered data, such as displaying an employee or item list scheme in an organizational chart (one or more components of the parent product, and those components may have child components, or other parent-level product components. Recursive CTE can greatly simplify the Code required to run recursive queries in SELECT, INSERT, UPDATE, DELETE, or create view statements. In earlier versions of SQL Server, recursive queries usually require temporary tables, cursors, and logic to control recursive step flows. For more information about common table expressions, see use common table expressions. Pseudocode and Semantics ---------------------------- The recursive CTE structure must contain at least one positioning point member and one recursive member. The following pseudo code shows a simple recursive CTE component that contains an anchor point member and a recursive member. WITH cte_name (column_name [,... n]) AS ( CTE_query_definition -- Anchor member is defined. UNION ALL CTE_query_definition -- Recursive member is defined referencing cte_name. ) -- Statement using the CTE SELECT * FROM cte_name The semantics of Recursive Execution is as follows: Split the CTE expression into positioning point Members and recursive members. Run the positioning point member to create the first call or benchmark result set (T0 ). Run recursive members and use Ti as the input and Ti + 1 as the output. Repeat Step 3 until an empty set is returned. Returned result set. This is the result of executing union all on T0 to Tn. Example ---------------------------- Original table: Now there is a requirement to query all the cities and zones (the query result includes the province) that save a certain amount of data ). If you only use SQL statements, you need to use technologies such as cursors and temporary tables. However, you can also use CTE in SQL Server2005. WITH district AS ( -- Obtain the first result set and update the final result set. SELECT * FROM t_tree WHERE id = 0 UNION ALL -- The following select statement first queries parent_id Based on the id value obtained from the previous query result set. -- The value of the field, then district will change the current query result set, and continue to execute the following select statement -- If the result set is not null, It is merged with the final query result and updated with the merged result -- Query result; otherwise, the execution is stopped. The district result set is the final result set. SELECT a. * FROM t_tree aINNER JOIN district bONa. parent_id = B. id ) SELECT * FROM district Query results: For more information about using a public table expression, see recursive queries using a public table expression.