Recursive queries using CTE (common table expressions)

This article reprinted: http://www.cnblogs.com/shuangnet/archive/2013/03/22/2975929.html

 

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.

The basic syntax structure of CTE is as follows:

WITH expression_name [(column_name [,... n])] AS (CTE_query_definition) -- The column name list is optional only when different names are provided for all result columns in the query definition. -- Run the CTE statement: SELECT <column_list> FROM expression_name;

Pay attention to the following points when using CTE:

The CTE must be followed by the SQL statements that use the CTE (such as select, insert, and update). Otherwise, the CTE will become invalid. The following SQL statement cannot use CTE normally:

With cr as (select * from table name where condition) -- select * from person. CountryRegion -- if this clause is added, the following cr error will be returned: select * from cr

2. The CTE can be followed by other CTE, but only one with can be used. Multiple CTE are separated by commas (,), as shown in the following SQL statement:

Withcte1 as (select * from table1 where name like 'test % '), cte2 as (select * from table2 where id> 20 ), cte3 as (select * from table3 where price <100) select. * from cte1 a, cte2 B, cte3 c where. id = B. id and. id = c. id

3. if the expression name of the CTE is the same as that of a data table or view, the SQL statement followed by the CTE still uses the CTE. Of course, the following SQL statement uses the data table or view.

4. The CTE can reference itself, or reference the pre-defined CTE in the same WITH clause.

5. the following clause cannot be used in CTE_query_definition:

• COMPUTE or COMPUTE
• Order by (unless TOP clause is specified)
• INTO
• OPTION clause with query prompt
• FOR XML
• FOR BROWSE

6. If you use CTE in a statement that is part of the batch processing, the statement before it must end with a semicolon, as shown in the following SQL:

Declare @ s nvarchar (3) set @ s = 'test % '; -- The witht_tree as (select * from table where field like @ s) select * from t_tree