SQL recursive query (with CTE as)

http://www.cnblogs.com/xqhppt/archive/2011/02/15/1955366.html 2011-02-15 16:41 by worry Summer,2415Read,0Reviews,Collection, Edit withCTE as
(
SelectId,pid,deptname,0 aslvl fromDepartment
whereId=2
Union All
SelectD.ID,D.PID,D.DEPTNAME,LVL+1 fromCTE CInnerJoinDepartment D
onc.id=D.pid
)
Select* fromCTE

 with AS
(
    Select Id,pid,deptname, 0  as   from Department
    where=2
    Union  All
    Select D.ID,D.PID,D.DEPTNAME,LVL + 1  from  Innerjoin  Department D
    on = d.pid
)
Select *  from CTE

1 Table structure

Id Pid Deptname
----------- ----------- --------------------------------------------------
10Headquarters
21Research Department
31Testing Department
41Quality Department
52Group 1
62Group 2
73Test 1
83Test 2
95Front End Group
Ten5Artwork

Id          Pid         deptname
-- --------- ----------- --------------------------------------------------
1 0            Headquarters
2 1            Research Department
3 1            Testing Department
4 1            Quality Department
5 2            Group 1
6 2            Group 2
7 3            Test 1
8 3            Test 2
9 5            Front End Group
Ten 5            Artwork

2 results Check all subordinate departments of department id=2 and this level

Id Pid deptname lvl
----------- ----------- -------------------------------------------------- -----------
21Research Department0
52Group 11
62Group 21
95Front End Group2
Ten5Artwork2

(5row affected)

Id          Pid         deptname                                           lvl
-- --------- ----------- -------------------------------------------------- -----------
2 1            Research and                                                Development Department 0
5 2            Group 1                                                1
6 2            Group 2                                                1
9 5            Front End                                                Group 2
Ten 5            Art                                                  2

(5  rows affected)

3 principle (excerpt from the Internet)

A recursive CTE contains at least two queries (also known as members). The first query is a fixed-point member, and the fixed-point member is simply a query that returns a valid table for the underlying or anchor point of the recursion. The second query is referred to as a recursive member, so that the query is called a recursive member is a recursive reference to the CTE name is triggered. The internal application of the CTE name can logically be interpreted as the result set of the previous query.

Recursive queries do not have an explicit recursive termination condition, and only stop recursion if the second recursive query returns an empty result set or exceeds the maximum limit for the recursion count. The method that refers to the upper limit of recursion is to use maxrecurion.