Sqlserver: CTE function processing recursion (with syntax)

When we do classification, we always encounter recursive processing. For example, the region is an example. China -- Beijing -- Xicheng district, we can store such information in a data table, use parentid to distinguish between the root node and the leaf node. If we want to do navigation, we get "Xicheng district", but we need to get his parent level or husband level, one way is to useProgramIs also very simple, another way is to use the database function. Since the database can do this, why the program?

This can also be handled in versions earlier than sqlserver2005, but stored procedures were used at the time,CodeThere are also many tables. During table design, a field indicating "depth" is added. At that time, I also wrote relatedArticle, See:

Unlimited storage process classification (1)

Unlimited classification of stored procedures (2)

Unlimited storage process classification (3)

However, when learning the new features of sqlserver2005, I noticed that the with statement can implement this function, and the code is simple, concise, and the most important thing is a good understanding.

The following is a recursive table structure:

The parentid records the relationship between them;

For example, what if we want to query data with ID = 10008 and parentid is 10008? For example:

Well, there's no suspense. Go directly to the code! In fact, it is easy to understand:

With categoryinfo (

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

 

Check whether it is simple, concise, and easy to understand!

Try it now!

This is a top-down query. If it is a bottom-up query, how can it be checked? Let's just put it apart!

 

Tag: sqlserver, with statement, with, recursion, infinite Recursion