SQL Server tree Query

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

Use City;gocreate table Tree (ID int identity (primary) key is not NULL, Name varchar () is not NULL, Parent varchar (20) NULL) Goinsert Tree values (' university ', NULL) Insert tree values (' College ', ' University ') Insert tree values (' Computer Academy ', ' College ') insert tree values (' Web Engineering ', ' Computer Academy ') Insert tree values (' Information management ', ' Computer Academy ') Insert tree values (' Telecom Academy ', ' College ') insert tree values (' Academic Office ', ' university ') insert Tree values (' Materials section ', ' Dean's Office ') insert Tree values (' Admissions Office ', ' university ') Gowith CTE as (-->begin an anchor member select ID, Name,parent,cast ( Name as nvarchar (max)) as te,0 as Levle from Tree where the Parent is Null-->end union all-->begin a recursive member of select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) + ' |_ ' +tree.name as nvarchar (MAX)) as te,levle+1 as Levle from Tree INNER join CTE on TREE.PARENT=CTE. Name-->end) SELECT * from the CTE order by Id--1. Splits a CTE expression into an anchor member and a recursive member. --2. Run an anchor member to create the first call or datum result set (T0). --3. Run recursive members, use Ti as input (there is only one record here), and ti+1 as 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.

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 traverses the 1th record: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:

Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) + ' |_ ' +tree.name as nvarchar (MAX)) as Te,levle+1 As Levlefrom tree inner join (select ID, Name,parent,cast (Name as nvarchar (max)) as te,0 as Levle from tree where Parent is null) as Cteon 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

Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) + ' |_ ' +tree.name as nvarchar (MAX)) as Te,levle+1 As Levlefrom Tree INNER JOIN (SQL statement for second recursive query) as Cteon tree.parent=cte. Name

The results are as follows:

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

Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) + ' |_ ' +tree.name as nvarchar (MAX)) as Te,levle+1 As Levlefrom Tree INNER JOIN (the result set of the last recursive query, just the last time, not the previous sum result set) as Cteon 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:

with CTE as (-->begin One anchor member select ID, Name,parent,cast (Name as nvarchar (max)) as TE, Row_number () over (order by GETDATE ()) as Orderi D--The most critical is the above field, to get the sort field, sorted by string. --Where the window function must use order BY, but cannot use the integer type, then use the time bar from the Tree where the Parent is null-->end UNION ALL-->begin a recursive member Select Tree.id, Tree.name,tree.parent,cast (Replicate (", Len (cte.te)) + ' |_ ' +tree.name as nvarchar (MAX)) as TE, CTE.O Rderid*100+row_number () over (Order by GETDATE ()) as OrderID from the Tree inner join CTE on TREE.PARENT=CTE. Name-->end) SELECT * from Cteorder by LTRIM (OrderID)--Finally the integer data is converted to a string type of sorting--sometimes the integer can be larger than the size, the string can be, the size of the string is a single character Comparison--integer + string = = Integer, only string + string = = two strings and--recursive query: The second record can refer to the value of the first record-the record identifier of the same level when dynamically loading the record: RowNumber () over (order by GETDATE ())--Extension: You can dynamically acquire a sub-department under a department. You can also get the department of the Department's superiors-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.

SQL Server tree Query