SQL Server CTE recursive query Oracle recursive query

In the database design of this piece, many times the table data model is a typical two-fork tree structure.

So when querying the data, it involves the recursive query of the data. There are two types of recursive queries: 1. Query itself and all child nodes from the root node; 2. query itself and all parent nodes from child nodes. The following are examples of Oracle 11g database and MSSQL 2008 database, to achieve the above two types of recursive query first build a table name: tadministrative

Field name	Description
Administrativeid	Administrative number
Administrativepid	Previous administrative level number at the current administrative level
Administrativename	Current administrative level name
Administrativeremark	Current administrative level notes

MSSQL: 1. Querying from the root node to child nodes----from the root node to the child node with cet_administrative (Administrativeid,administrativepid,administrativename,administrativeremark ) as (----fixed data select Administrativeid,administrativepid,administrativename,administrativeremark from Tadministrative WHERE Administrativepid is nullunion all----recursive data select T.administrativeid,t.administrativepid,t. Administrativename,t.administrativeremarkfrom tadministrative tinner JOIN cet_administrative on CET_ Administrative.administrativeid=t.administrativepid) SELECT Administrativeid,administrativepid, Administrativename,administrativeremark from Cet_administrativeoption (maxrecursion 0); GO 2. Then the child node is queried to the root nodeWith Cet_administrative (Administrativeid,administrativepid,administrativename,administrativeremark) as (---- Fixed data Select Administrativeid,administrativepid,administrativename,administrativeremark from Tadministrative WHERE Administrativeid = ' 25d8c293b5a448e9b821558a0afdb0a2 ' UNION all----recursive data select T.administrativeid,t. Administrativepid,t.administrativename,t.administrativeremarkfrom tadministrative TINNER JOIN CET_Administrative on Cet_administrative.administrativepid=t.administrativeid) SELECT Administrativeid,administrativepid, Administrativename,administrativeremark from Cet_administrativeoption (maxrecursion 0); GO Oracle: 1. From the root node to the child node:SELECT Administrativeid,administrativepid,administrativename,administrativeremark from Tadministrative START with Administrativepid is a NULL CONNECT by PRIOR Administrativeid=administrativepid; 2. Query from child nodes to the root node:SELECT Administrativeid,administrativepid,administrativename,administrativeremark from Tadministrative START with Administrativeid = ' 25d8c293b5a448e9b821558a0afdb0a2 ' CONNECT by PRIOR Administrativepid=administrativeid;the traversal of the two from the root node to the child node is not the same, Oracle is the first order traversal, as for the MSSQL is actually an iterative implementation. You can look at the CTE explanation for MSDN:http://msdn.microsoft.com/zh-cn/library/ms186243 (v=sql.90). aspx.  The syntax of MSSQL is as follows:

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

递归执行的语义如下：

1. 将 CTE 表达式拆分为定位点成员和递归成员。

2. Run an anchor member to create the first call or datum result set (T0).
3. Run the recursive member, use Ti as the input, and ti+1 as the output.
4. Repeat step 3 until the empty set is returned.
5. Returns the result set. This is the result of the T0 to Tn execution of UNION all.

Anchor member is definedThe location of the query is the data setT0, and then Recursive member is defined referencing Cte_nameThis position begins by saying that the data of the T0 is taken into account based on the conditions to do the calculation to get the T1, in turn, until the band TI for the empty set ends (of course, not unrestricted recursion, the reader himself to see the explanation of MSDN). The Oracle syntax is as follows:SELECT ... From TABLENAME START withCOND1 CONNECT byCOND2 WHERECOND3; START withIs the starting condition, i.e. where the first data begins CONNECT byIs the connection condition, that is, the relationship between the data set and the next data set, which is START with Administrativeid = ' 25d8c293b5a448e9b821558a0afdb0a2 ' Connect by PRIOR Administrativepid=administrativeid; That is, when a child node queries to the root node, the first node is obtained, and then the join condition Administrativepid=administrativeid equals to the left of the child node is the parent node number, the right side is the parent section number, that is, find the "parent node number of the current child node = parent Node Number" The collection.

SQL Server CTE recursive query Oracle recursive query