TSQL layering and recursive queries

1, demand analysis

Implement hierarchies in TSQL, such as a data structure, province, city, County, township, village, how to use a table to represent this data structure, and allow is asymmetric, for example, Shanghai is a municipality, there is no province.

Create Table dbo.hierarchy (ID  intnotnullprimarykey,  --type int not NULL,intnotnullvarchar(  -  not NULL )

The type represents the types, which can be set: Province, type is 1, city, type is 2, and so on.

ParentID identifies the parent ID, such as Xinyang's parentid is the ID of Henan province.

2. Insert test data

The test data format describes the attribution relationship, bloggers lazy, remove the Type field.

Insert  intoDbo.hierarchyValues(1,0,'Henan Province')
,(2,1,'Xinyang'),(3,2,'Huaibin County'),(4,3,'Lu Ji Xiang'),( A,3,'Tang Wan Xiang'),( -,3,'Tai'),( -,3,'Yu Kang')              ,(8,2,'Gushi County'),(9,8,'Li Dian Xiang')              ,(Ten,2,'Xi County'),( One,Ten,'Closing Township'),(5,1,'Anyang'),(6,5,'Hua'),(7,6,'Lao Miao Xiang'),( the,1,'Nanyang'),( -, the,'Fangcheng County'),( -,1,'Zhumadian'),( -, -,'Zhengyang')Select *  fromDbo.hierarchyOrder  byParentID

3, recursive query

As the actual data may be many, so, to obtain all the city, county, township, village and so on, must use recursive query, how to write a recursive query? Bloggers are not very familiar with recursive queries, but have a simple understanding

• 1, initial conditions
• 2, call itself
• 3, termination conditions

4, in TSQL, the CTE can be very good to support recursive query, recursive query does not support sub-query

; withCte as(Select *  fromDbo.hierarchywhereId=1Union  AllSelect *  fromDbo.hierarchywhereParentIDinch(SelectId fromCTE) andId not inch(SelectId fromCTE))Select * fromCte

SQL Server Error

MSG 465, Level A, State 1, line 11
Recursive references is not allowed in subqueries.

5, modify the subquery to inner JOIN

; withCTE (Id,parentid,name) as(Select *  fromDbo.hierarchywhereId=1Union  AllSelectH.*  fromDbo.hierarchy HInner JoinCTE C onH.parentid=c.id--where C.id!=h.id)Select * fromCTEOrder  byParentID

If you want to see how many level of inward recursion you can use a derived column, Level=0 is the province level,level=1 is the city level, and so on.

; withCTE (Id,parentid,name, Level) as(SelectId,parentid,name,0  as  Level fromDbo.hierarchywhereId=1Union  AllSelectH.id,h.parentid,h.name,c. Level+1  as  Level fromDbo.hierarchy HInner JoinCTE C onH.parentid=c.id--where C.id!=h.id)Select * fromCTEOrder  byParentID

6,tsql CTE Recursive query principle (excerpt from online)

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 of limiting the maximum number of recursion times is to use maxrecurion.

TSQL layering and recursive queries