SQL with (recursive CTE query)

This article from: http://www.cnblogs.com/smailxiaobai/archive/2012/01/16/2323291.html

Specifies a temporary named result set, which is called a common table expression (CTE). The expression originates from a simple query and is defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in the CREATE VIEW statement as part of the statement's SELECT definition statement. A common table expression can include a reference to itself. This type of expression is called a recursive common table expression.

Transact-SQL Syntax conventions

Grammar

[With <common_table_expression> [,... N]]<common_table_expression>::= expression_name [(Column_nam e [,... N])] as (cte_query_definition)

Parameters

Expression_name

A valid identifier for the common table expression. Expression_name must be different from the name of any other common table expression defined in the same 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 common table expression instead of a base object.

column_name

Specify the column name in the common table expression. Duplicate names are not allowed in a CTE definition. The specified column an array must match the number of columns in the cte_query_definition result set. The column Name list is optional only if you provide a different name for all the result columns in the query definition.

CTE_query_definition

Specifies a SELECT statement whose result set fills the common table expression. Except that the CTE cannot define another CTE, thecte_query_definition SELECT statement must meet the same requirements as when creating the view. For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

If more than one cte_query_definitionis defined, these query definitions must be joined together with one of the following set operators: Union ALL, Union, EXCEPT, or INTERSECT. For more information about how to use recursive CTE query definitions, see the Remarks section below and recursive queries that use common table expressions.

Notes Guidelines for creating and using CTE

The following guidelines apply to non-recursive CTE. For guidelines for recursive CTE, see "Guidelines for defining and using recursive CTE" later in this article.

• The CTE must follow a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all of the CTE columns. You can also designate a CTE as part of a SELECT definition statement in a view in the CREATE view statement.
• Multiple CTE query definitions can be defined in a non-recursive CTE. The definition must be used in conjunction with one of the following set operators: Union ALL, Union, INTERSECT, or EXCEPT.
• The CTE can refer to itself, or it can refer to a pre-defined CTE in the same with clause. Forward references are not allowed.
• It is not allowed to specify multiple with clauses 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 clauses cannot be used in cte_query_definition :
  
  • COMPUTE or COMPUTE by
  • ORDER by (unless the TOP clause is specified)
  • Into
  • OPTION clause with query hint
  • For XML
  • For BROWSE
• If you use a 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 a CTE to define a cursor.
• Tables in the remote server can be referenced in the CTE.
• When a CTE is executed, any hint that references a CTE may conflict with the other hints found when the CTE accesses its underlying table, which conflicts with the hint of the view in the reference query. When this happens, the query returns an error. For more information, see View Resolution.

Guidelines for defining and using recursive CTE

The following guidelines apply to the case of defining a recursive CTE:

• A recursive CTE definition must contain at least two CTE query definitions, an anchor member, and a recursive member. You can define multiple anchor members and recursive members, but you must place all of the anchor member query definitions before the first recursive member definition. All CTE query definitions are anchor members, except when they refer to the CTE itself.
• Anchor members must be used in conjunction with one of the following set operators: Union ALL, Union, INTERSECT, or EXCEPT. Only the UNION all set operator can be used between the last anchor member and the first recursive member, and when multiple recursive members are combined.
• The number of columns in the anchor member and the recursive member must be the same.
• The data type of the column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
• The FROM clause of a recursive member can only refer to the CTE expression_nameonce.
• The following items are not allowed in the cte_query_definition of Recursive members:
  
  • SELECT DISTINCT
  • GROUP by
  • Having
  • Scalar aggregation
  • TOP
  • Left, right, OUTER join (allows INNER join to appear)
  • Sub-query
  • A hint that is applied to a recursive reference to a CTE in cte_query_definition .

The following guidelines apply to the use of recursive CTE:

• All columns returned by the recursive CTE can be empty, regardless of the nullability of the columns returned by the participating SELECT statement.
• If the recursive CTE combination is incorrect, an infinite loop may result. For example, if the recursive member query definition returns the same value for the parent Arrays parent column, an infinite loop is created. You can limit the number of recursive levels allowed by a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement to prevent an infinite loop. This allows the execution of the statement to be controlled before the code problem that generated the loop is resolved. The server-wide default value is 100. If you specify 0, there is no limit. You can specify only one Maxrecursion value per statement. For more information, see Query hints (Transact-SQL).
• You cannot update data by using a view that contains a recursive common table expression.
• You can use a CTE to define cursors on a query. A CTE is a select_statement parameter that defines a cursor result set. Recursive CTE allows only fast forward-only cursors and static (snapshot) cursors to be used. If a different cursor type is specified in the recursive CTE, the type is converted to a static cursor type.
• Tables in the remote server can be referenced in the CTE. If a remote server is referenced in a recursive member of the CTE, a spool is created for each remote table so that the tables can be accessed repeatedly locally.

Example A. Creating a simple common table expression

The following example shows the number of employees reported directly to each manager of Adventure Works Cycles.

Use AdventureWorks; Gowith dirreps (ManagerID, Directreports) as (    SELECT ManagerID, COUNT (*) from     HumanResources.Employee as E    WHERE ManagerID    is not a NULL GROUP by ManagerID) SELECT ManagerID, directreports from Dirreps ORDER by ManagerID; GO

B. Using common table expressions to limit the number of times and average reports

The following example shows the average number of employees reported to the manager.

With Dirreps (Manager, Directreports) as (    SELECT ManagerID, COUNT (*) as Directreports    from HumanResources.Employee    GROUP by ManagerID) SELECT AVG (directreports) as [Average number of Direct Reports]from Dirre PS WHERE directreports>= 2; GO

C. Referencing the same common table expression multiple times

The following example displays the SalesOrderHeader total number of sales orders for each salesperson in the table and the date of the most recent sales order. The CTE is referenced two times in a running statement: one time to return the column selected by the salesperson, and another to retrieve similar details for the sales manager. Data for sales and sales managers are returned in one row.

Use AdventureWorks; Gowith sales_cte (SalesPersonID, Numberoforders, MaxDate) as (    SELECT SalesPersonID, COUNT (*), MAX (OrderDate)    From Sales.SalesOrderHeader    GROUP by SalesPersonID) SELECT E.employeeid, OS. Numberoforders, OS. MaxDate,    E.managerid, OM. Numberoforders, OM. Maxdatefrom HumanResources.Employee as E    JOIN sales_cte as OS on    e.employeeid = os. SalesPersonID left    OUTER joins Sales_cte as om on    e.managerid = om. Salespersonidorder by E.employeeid; GO

Use recursive common table expressions to display multiple levels of recursion.

The following example displays a hierarchical list of managers and employees who report to the manager.

Use AdventureWorks; Gowith directreports (ManagerID, EmployeeID, Employeelevel) as (    SELECT ManagerID, EmployeeID, 0 as Employeelevel From    HumanResources.Employee    WHERE ManagerID is    a NULL UNION all    SELECT E.managerid, E.employeeid, Employeelevel + 1    from HumanResources.Employee e        INNER JOIN directreports d on        e.managerid = D.employeeid) SELECT ManagerID, EmployeeID , Employeelevel from Directreports; GO

E. Use a recursive common table expression to display the two levels of recursion.

The following example shows the manager and the employee who reported to the manager. The number of levels returned is limited to two.

Use AdventureWorks; Gowith directreports (ManagerID, EmployeeID, Employeelevel) as (    SELECT ManagerID, EmployeeID, 0 as Employeelevel From    HumanResources.Employee    WHERE ManagerID is    a NULL UNION all    SELECT E.managerid, E.employeeid, Employeelevel + 1    from HumanResources.Employee e        INNER JOIN directreports d on        e.managerid = D.employeeid) SELECT ManagerID, EmployeeID , employeelevel from Directreports WHERE employeelevel <= 2; GO

F. Using a recursive common table expression to display a hierarchical list

The following example adds the names of managers and employees, and their respective titles, on the basis of example C. The hierarchy of managers and employees is highlighted by indenting the various levels.

Use AdventureWorks; Gowith directreports (Name, Title, EmployeeID, Employeelevel, Sort) as ("SELECT CONVERT" (varchar (255), C.firstname + "+ C.L Astname),        e.title,        E.employeeid,        1,        CONVERT (varchar (255), C.firstname + "+ c.lastname)    from HumanResources.Employee as E    JOIN person.contact as C on e.contactid = C.contactid     WHERE e.managerid is NULL    U Nion all    SELECT CONVERT (varchar (255), REPLICATE (' | ', employeelevel) +        C.firstname + ' + c.lastname),        e.title,        E.employeeid,        employeelevel + 1,        CONVERT (varchar (255), RTRIM (Sort) + ' |  ' + FirstName + ' +                  LastName) from    HumanResources.Employee as e    joins Person.Contact as C on e.contactid = C.contactid    JOIN directreports as D on e.managerid = D.employeeid    ) SELECT EmployeeID, Name, Title, Employeeleve Lfrom directreports ORDER by Sort; GO

G. Using Maxrecursion to cancel a statement

Can be used MAXRECURSION to prevent unreasonable recursive CTE from entering an infinite loop. The following example deliberately creates an infinite loop, and then uses MAXRECURSION the hint to limit the recursion level to level two.

Use AdventureWorks; Go--creates an infinite loopwith CTE (EmployeeID, ManagerID, title) as (    SELECT EmployeeID, ManagerID, title from    H Umanresources.employee    WHERE ManagerID is not a NULL  UNION all    SELECT CTE. EmployeeID, CTE. ManagerID, CTE. Title from the    CTE     JOIN  HumanResources.Employee as E on         CTE. ManagerID = E.employeeid)--uses maxrecursion to limit the recursive levels to 2SELECT EmployeeID, ManagerID, Titlefrom CTE OPTION (maxrecursion 2); GO

After correcting the code error, maxrecursion is no longer required. The following example shows the corrected code.

Use AdventureWorks; Gowith CTE (EmployeeID, ManagerID, title) as (    SELECT EmployeeID, ManagerID, title from    humanresources.employee< C2/>where ManagerID is not  a NULL UNION all    SELECT  E.employeeid, E.managerid, E.title    from HumanResources.Employee as E    JOIN cte on e.managerid = CTE. EmployeeID) SELECT EmployeeID, ManagerID, Titlefrom CTE; GO

H. Using common table expressions to selectively perform recursive operations in SELECT statements

The following example shows the ProductAssemblyID = 800 product assembly and part hierarchies required for the production of bicycles.

Use AdventureWorks; Gowith Parts (Assemblyid, ComponentID, Perassemblyqty, EndDate, Componentlevel) as (    SELECT B.productassemblyid, B. ComponentID, B.perassemblyqty,        b.enddate, 0 as Componentlevel from    production.billofmaterials as b    WHERE b . ProductAssemblyID = B.enddate is a          NULL    UNION all    SELECT BOM. ProductAssemblyID, BOM. ComponentID, P.perassemblyqty,        BOM. EndDate, Componentlevel + 1 from    production.billofmaterials as BOM         INNER JOIN Parts as P on        BOM. ProductAssemblyID = P.componentid and        BOM. EndDate is NULL) SELECT Assemblyid, ComponentID, Name, Perassemblyqty, EndDate,        componentlevel from Parts as P    I Nner JOIN production.product as PR on    p.componentid = pr. Productidorder by Componentlevel, Assemblyid, ComponentID; GO

I. Using a recursive CTE in an UPDATE statement

The following example ManagerID 12 increases the value of all employees reported directly or indirectly by VacationHours 25%. A common table expression returns a hierarchical list of employees, such as those reported directly to the ManagerID 12 report, as well as employees who report directly to those employees. Only the rows returned by the common table expression are modified.

Use AdventureWorks; Gowith directreports (EmployeeID, Newvacationhours, Employeelevel) as (SELECT E.employeeid, e.vacationhours, 1 from  HumanResources.Employee as e  WHERE e.managerid =  UNION all  SELECT E.employeeid, E.vacationhours, Employeelevel + 1 from  HumanResources.Employee as e  joins Directreports as D on e.managerid = D.employeeid) UPDATE H Umanresources.employeeset vacationhours = vacationhours * 1.25FROM HumanResources.Employee as Ejoin DirectReports as D on E.employeeid = D.employeeid; GO

Using multiple anchor points and recursive members

The following example uses multiple anchor points and recursive members to return all ancestors of the specified person. A table is created and values are inserted into the table to establish the lineage returned by the recursive CTE.

--Genealogy Tableif object_id (' person ', ' U ') was not a NULL DROP TABLE person; Gocreate TABLE person (ID int, Name varchar (+), mother int, Father int); Goinsert person VALUES (1, ' Sue ', NULL, NULL), insert person values (2, ' Ed ', null, NULL); Insert person VALUES (3, ' Emma ', 1, 2) Insert person VALUES (4, ' Jack ', 1, 2), insert person values (5, ' Jane ', null, NULL); Insert person VALUES (6, ' Bonnie ', 5, 4); INSERT person VALUES (7, ' Bill ', 5, 4); go--Create The recursive CTE to find all of Bonnie ' s ancestors.    With Generation (ID) as (--first anchor member returns Bonnie ' s mother.    SELECT mother from person WHERE Name = ' Bonnie ' union--Second anchor member returns Bonnie ' s father. SELECT Father from person WHERE Name = ' Bonnie ' UNION all--first recursive member returns male ancestors of the pre    Vious generation. SELECT Person.father from Generation, person WHERE generation.id=person.idunion all--Second Recursive member return    s female ancestors of the previous generation. SELECT Person.mother from Generation, person WHERE generation.id=person.id) SELECT person.id, Person.name, Person.mother , Person.fatherfrom Generation, personwhere generation.id = person.id; GO

See http://msdn.microsoft.com/zh-cn/library/ms175972 (v=sql.90). aspx for details

A data table (T_tree): The data in the table has three fields: ID, Node_name, parent_id. In fact, this table holds a tree structure, divided into three layers: province, city, and district. Where ID represents the ID number of the current province, city, or region, Node_name represents the name, and parent_id represents the ID of the node's parent node.
Now there is a need to query out all the cities and districts Below a province (the query result contains a province). If you use only SQL statements, you need to use techniques such as cursors, temporal tables, and so on. However, you can also use a CTE in SQL Server2005.
From this point of view belongs to recursive call, that is to find the record of the province to meet the price adjustment, in this case to check the "Liaoning province" record, as follows:
ID Node_name parent_id
1 Liaoning Province 0
Then check all records with the parent_id field value of 1, as follows:
ID Node_name parent_id
2 Shenyang, 1
3 Dalian City 1
Finally, check the records for the parent_id field value of 2 or 3, as follows:
ID Node_name parent_id
4 Dadong 2
5 Shenhe District 2
6 Tiexi District 2
Merging the above three result sets is the final result set.
The above query process can also be interpreted according to the process of recursion, that is, the record of the designated province (Liaoning province), the record is obtained, the corresponding ID value, and then entered the recursive process, as shown in.
　　

As can be seen from the above, the recursive process is the process of merging query result sets with union all, which is equivalent to the following recursive formula:
ResultSet (n) = ResultSet (n-1) Union Allcurrent_resultset
where ResultSet (n) represents the final result set, ResultSet (N-1) represents the second-to-last result set, Current_resultset represents the result set currently found, and the recordset that initially queries "Liaoning Province" is equivalent to the initial conditions of recursion. The end condition of recursion is that Current_resultset is empty. Here is the pseudo-code for this recursive procedure:
Publicresultsetgetresultset (ResultSet)
{
if (resultsetisnull)
{
current_resultset= The first result set (contains a recordset for a province)
Save the ID of the result set in the collection
Getresultset (Current_resultset)
}
Current_resultset= isolate the current result set based on the ID value in the ID collection
if (current_resultisnull) returnresultset
Saves the ID of the current result set in the collection
Return Getresultset (Resultsetunionallcurrent_resultset)
}
Get the final result set
Resultset=getresultset (NULL)
As can be seen from the above process, this recursive process is more complex, but the CTE provides us with a simple syntax to simplify the process.
The CTE syntax for implementing recursion is as follows:
[With[,n]]
::=
expression_name[(Column_name[,n])
As (
Cte_query_definition1--anchor member (i.e., initial value or first result set)
UnionAll
Cte_query_definition2--Recursive members
)
The following is an SQL statement that uses a recursive CTE to obtain information about the "Liaoning Province" and all the cities and districts below:
With
Districtas
(
--Get the first result set and update the final result set
Select*fromt_treewherenode_name=n ' Liaoning Province '
UnionAll
--The following SELECT statement first queries the PARENT_ID based on the ID value obtained from the previous query result set
--The value of the field, and then district will change the current query result set and continue executing the following SELECT statement
--If the result set is not NULL, merge with the final query result, and update the final check with the results of the merge
--Poll the results; otherwise stop execution. The final district result set is the final result set.
Selecta.*fromt_treea,districtb
Wherea.parent_id=b.id
)
Select*fromdistrict
The results of the query are as shown.

The following CTE queries for non-leaf nodes:
With
Districtas
(
Select*fromt_treewherenode_name=n ' Liaoning Province '
UnionAll
Selecta.*fromt_treea,districtb
Wherea.parent_id=b.id
),
District1as
(
Selecta.*fromdistrictawherea.idin (selectparent_idfromdistrict))
Select*fromdistrict1
The query results are as shown.

Note: Only the "Liaoning Province" and "Shenyang City" have the following nodes.
The following points should be noted when defining and using recursive CTE:
1. The recursive CTE definition must contain at least two CTE query definitions, one anchor member and one recursive member. You can define multiple anchor members and recursive members, but you must place all of the anchor member query definitions before the first recursive member definition. All CTE query definitions are anchor members, except when they refer to the CTE itself.
2. The anchor member must be used in conjunction with one of the following set operators: Union ALL, Union, INTERSECT, or EXCEPT. Only the UNION all set operator can be used between the last anchor member and the first recursive member, and when multiple recursive members are combined.
3. The number of columns in the anchor member and the recursive member must be the same.
4. The data type of the column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
5. The FROM clause of a recursive member can only refer to the CTE expression_name once.
6. The following items are not allowed in the cte_query_definition of the recursive member:
(1) SELECT DISTINCT
(2) GROUP by
(3) having
(4) Scalar aggregation
(5) TOP
(6) left, right, OUTER join (allows INNER join to appear)
(7) Sub-query
(8) A hint that applies to recursive references to the CTE in CTE_query_definition.
7. Regardless of the nullability of the column returned by the participating SELECT statement, all columns returned by the recursive CTE can be empty.
8. If the recursive CTE combination is incorrect, an infinite loop may result. For example, if the recursive member query definition returns the same value for the parent Arrays parent column, an infinite loop is created. You can limit the number of recursive levels allowed by a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement to prevent an infinite loop. This allows the execution of the statement to be controlled before the code problem that generated the loop is resolved. The server-wide default value is 100. If you specify 0, there is no limit. You can specify only one Maxrecursion value per statement.
9. You cannot update data by using a view that contains a recursive common table expression.
10. You can use a CTE to define cursors on a query. Recursive CTE allows only fast forward-only cursors and static (snapshot) cursors to be used. If a different cursor type is specified in the recursive CTE, the type is converted to a static cursor type.
11. Tables in the remote server can be referenced in the CTE. If a remote server is referenced in a recursive member of the CTE, a spool is created for each remote table so that the tables can be accessed repeatedly locally.

SQL with (recursive CTE query)