SQL Server 2005 uses a relational database to store the CFG

This article describes information that uses a relational database to store the CFG (Control flowchart). In the query, a CTE is used for recursive queries in order to obtain the results of all dominate relationships.

This article describes information that uses a relational database to store the CFG (Control flowchart). In the query, a CTE is used for recursive queries in order to obtain the results of all dominate relationships.

The simple example is as follows:

1. The existing table Testre structure is as follows

2. The result of the query is to find all the PID of the item of id= ' A ', namely B, C, D.

3. Use a CTE to do a recursive query:

With Re_cte (IDs, PID, level)
Technorati Tags: sql server,cte, recursive, query as 
( 
    SELECT  ID, PID, 1 as [level]
  from  testre 
    WHERE id= ' A '  
    UNION all 
    SELECT  t.id, T.pid, [level] + 1 
    from  testre t  INNER JOIN Re_cte CT 
    On T.id=ct.pid 
) 
SELECT * from Re_cte

Where the level is optional

4. The results of the query are:

5. A simple description of the CTE (reference msdnhttp://msdn.microsoft.com/zh-cn/library/ms186243%28sql.90%29.aspx):

With Cte_name (column_name [,... n]) as cte_query_definition–-the Anchor member is 
defined. 
UNION all 
cte_query_definition–-recursive the defined referencing Cte_name. 
) 
--Statement using the CTE 
SELECT * from 
cte_name

A recursive CTE structure must contain at least one anchor member and one recursive member.

At the time of execution:

Splits a CTE expression into an anchor member and a recursive member.

Run the anchor member to create the first call or datum result set (T0).

Run the recursive member, the Ti as input, the ti+1 as output.

Repeat step 3 until the empty set is returned.

Returns the result set. This is the result of the execution of UNION all for T0 to Tn.