The CTE recursive query in SQL Server 2005 gets a tree

I feel this CTE recursive query is very good, let me give an example:

[C-sharp]View Plain copy print?

2. Use city ;
4. Go
6. CREATE TABLE Tree
8. (  
10. ID int identity (primary) key is not null,
12. Name varchar () is not null,
14. Parent varchar () null
16. )  
18. Go
20. Insert Tree VALUES (' university ',null)
22. Insert Tree VALUES (' College ','University ')
24. Insert Tree VALUES (' Computer Academy',' College ')
26. Insert Tree VALUES (' network Engineering ',' Computer Academy ')
28. Insert Tree VALUES (' information management ',' Computer Academy ')
30. Insert Tree VALUES (' Telecom Academy',' College ')
32. Insert Tree VALUES (' academic Office ',' university ')
34. Insert Tree VALUES (' materials section ',' Dean's Office ')
36. Insert Tree VALUES (' admissions Office ',' university ')
38. Go
40. With CTE as
42. (  
44. -->begin A locator point member
46. Select ID, Name,parent,cast (Name as nvarchar (max)) as te,0 as Levle from Tree where the Parent is null
48. -->end
50. Union All
52. -->begin a recursive member
54. Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) +' |_ ' +tree.name as nvarchar (MAX)) as te,levle+1 as Levle
56. From Tree INNER join CTE
58. On tree.parent=cte. Name
60. -->end
62. )  
64. SELECT * from the CTE order by ID
66. --1. Splitting a CTE expression into an anchor member and a recursive member.
68. --2. Run an anchor member to create the first call or datum result set (T0).
70. --3. Run recursive members, use Ti as input (there is only one record here), and ti+1 as output.
72. --4. Repeat step 3 until the empty set is returned.
74. --5. Returns the result set.  This is the result of the T0 to Tn execution of UNION all.

The above SQL statement inserts a single piece of data again:

Insert Tree VALUES (' Network 1 class ', ' Network Engineering ')

Running results such as:

Figure 1 Running results

Note: It seems that the field selected at the recursive member must be the data of the tree table, not the one in the CTE result set except the tree, and the fields in the CTE can be referenced here, such as the field Te.

First look at the SQL statement that iterates through the 1th record:

[C-sharp]View Plain copy print?

1. Select ID, Name,parent,cast (Name as nvarchar (max)) as te,0 as Levle from Tree where the Parent is null

The results obtained are:

Name Parent TE Levle

-------------------------------------

University of NULL University 0

The class SQL statement for the result collection that is obtained recursively for the 2nd time is:

[C-sharp]View Plain copy print?

1. select tree.id, tree.name,tree.parent,cast (Replicate (   ' , Len (cte.te)) + ' |_ ' +tree.name  as   nvarchar (MAX))   as  te,levle+1  as  levle   
2. from tree inner join  
3.     (Select id, name,parent,cast (name  as  nvarchar (max))   as  te,0  as  levle from tree  where parent  is   null )   
4. as  cte  
5. on  Tree.parent=cte. name  

The result of the above CTE subquery is the result set of the first recursive query, the SQL run result is:

Similarly, the above three records of the second recursive query are the ' locating members ' of the third query:

"Note here that the above three records are the input of the third recursive, starting with the last one, that is, the first is the record of the Id=9, followed by 7 and 2, and the fourth recursion is similar"

Third recursive collation SQL statement

[C-sharp]View Plain copy print?

1. Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) +' |_ '  +tree.name as nvarchar (MAX)) as te,levle+1 as Levle
2. From Tree INNER join
3. (SQL statement for second recursive query) as CTE
4. On tree.parent=cte. Name

The results are as follows:

In fact, each recursive class of SQL can be shown as follows:

[C-sharp]View Plain copy print?

1. Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) +' |_ '  +tree.name as nvarchar (MAX)) as te,levle+1 as Levle
2. From Tree INNER join
3. (The result set of the last recursive query, just the last time, not the previous sum result set)
4. As CTE
5. On tree.parent=cte. Name

The fourth time recursion and so on, and the result of the last query is the union of all the recursion above.

Continuation: In the above sq statement query results, the record with ID 10 should be placed after ID 4.

Add two records to the datasheet again:

Insert Tree VALUES (' Accounting section ', ' Computer Academy ')
Insert Tree values (' I ', ' Network 1 classes ')

Modify the above SQL statement again:

[C-sharp]View Plain copy print?

2. With CTE as
4. (     
6. -->begin A locator point member
8. Select ID, Name,parent,cast (Name as nvarchar (max)) as TE,
10. row_number () over (order by GETDATE ()) as OrderID
12. -The most critical is the above field, to get the sort field, sorted by string.
14. --where the window function must use order BY, but not the integer type, then use the time.
16. From Tree where the Parent is null
18. -->end
20. Union All
22. -->begin a recursive member
24. Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) +' |_ '+ Tree.name as nvarchar (MAX)) as TE,
26. CTE. Orderid*100+row_number () over (Order by GETDATE ()) as OrderID
28. From Tree INNER join CTE
30. On tree.parent=cte. Name
32. -->end
34. )     
36. SELECT * from CTE
38. ORDER by LTRIM (OrderID)--to sort this integer data into a string type
40.   
42. --Sometimes the integer can be larger than the size, the string is also possible, and the size of the string is a character comparison.
44. --int + string = = Integer, only string + string = = two string and and
46. --Recursive query: The second record can refer to the value of the first record
48. --record identifier of the same level when dynamically loading records: RowNumber () over (order by GETDATE ())
50. --Extension: You can dynamically get the sub-department under a department. You can also get the department's superior
52.   
54. --Summary: First to piece together an integer data, then converted to a string, and finally the order of the string, not the integer data order,

The final result is:

Figure 2 Running results

This way, no matter how many records a user inserts, they can be queried by department and by law.

The CTE recursive query in SQL Server 2005 gets a tree