sqlserver:cte function processing recursion (with syntax)

When we do classification processing, we always encounter recursive processing, such as the region is an example, China-Beijing-Xicheng District, we can store such information in a data table, using ParentID to distinguish between root node and leaf node. If we want to do navigation, get "Xicheng", but also to get his father, or father, one way is to use the program to deal with, is very simple, another way is to use the function of the database. Since the database can complete this matter, why use the program?

In previous versions of SqlServer2005, it was also possible to handle this situation, but at the time the stored procedure was used, the code was more, the table was designed, and a field representing "depth" was added, and I wrote the relevant article, see:

Unlimited class classification for stored procedures (1)

Unlimited class classification for stored procedures (2)

Unlimited class classification for stored procedures (3)

However, when learning the new features of SqlServer2005, it is important to note that the WITH statement can achieve such a function, and that the code is simple, concise, and, above all, a good understanding.

The following is a recursive table structure diagram:

The ParentID in the inside records the relationship between them;

For example, we want to query the ID = 10008, and ParentID 10008 of the data to do? Such as:

Oh, do not leave suspense, directly on the code bar! It's pretty easy to understand:

With CategoryInfo as (  select Id,text,parentid from Recursive WHERE id = 10008  UNION all  SELECT a.id,a.text,a. ParentID from Recursive as a,categoryinfo as b WHERE a.parentid = b.id  )  SELECT * from CategoryInfo

See if it is simple, concise, good understanding!

Give it a try!

This is from the top down, if you look from the bottom up, how to check it, you extrapolate it!

sqlserver:cte function processing recursion (with syntax)