Examples of non-cyclic recursive queries in the SQL Server tree table, tree Recursion

Examples of non-cyclic recursive queries in the SQL Server tree table, tree Recursion

Many people may want to query the content associated with the entire tree-like table through recursive loops... in fact, Microsoft can use other syntaxes in SQL2005 or later versions. The following is an example.

-- Query the parent node with tree as (SELECT * FROM Areas WHERE id = 6 -- the child id to be queried union all select Areas. * FROM Areas, tree where tree. PId = Areas. id) SELECT Area from tree -- query the subnode with tree as (SELECT * FROM Areas WHERE id = 7 -- The subid to be queried union all select Areas. * FROM Areas, tree where tree. id = Areas. PId) SELECT Area FROM TREE

The query result of the parent node through the subnode is:

Modify the code

-- Query the parent node declare @ area varchar (8000) through the subnode; with tree as (SELECT * FROM Areas WHERE id = 6 -- The subid to be queried union all select Areas. * FROM Areas, tree where tree. PId = Areas. id) select @ area = isnull (@ area, '') + Area from Tree order by id select Area = @ area

The result is: Fengtai District, Beijing, China.

Based on the above code, you can encapsulate this code as a stored procedure.

----- Stored procedure, recursively retrieve the tree-like region table string if exists (select * from sysobjects where name = 'SP _ GetAreaStr ') drop proc SP_GetAreaStrgocreate procedure SP_GetAreaStr @ id intasdeclare @ area varchar (8000) beginWITH tree as (SELECT * FROM Areas WHERE id = @ id -- The subid union all select Areas to be queried. * FROM Areas, tree where tree. PId = Areas. id) select @ area = isnull (@ area, '') + Area from Tree order by id select Area = @ areaend go -- exec sp_helptext 'SP _ GetAreaStr '-- goexec SP_GetAreaStr 28go

Query results: lingbi County, Suzhou City, Anhui Province, China

Table structure used:

Some data:

The above is a detailed description of the SQL Server tree table non-cyclic recursive query instances. I hope to help you. If you have any questions, please leave a message, the editor will reply to you in time!