SQL Server Tree Table Examples of non-cyclic recursive queries _mssql

Many people may want to query the entire tree table associated with the content will be returned through the loop return to check ... In fact, Microsoft can query with other syntax in SQL2005 or above, the following is an example.

--Querying the parent node with the subtree as 
( 
SELECT * from Areas 
WHERE id = 6)--The child ID 
UNION all 
SELECT areas.* F to query ROM Areas, tree 
WHERE tree. PId = areas.id
) 
SELECT area from tree
-queries the child nodes with the tree as 
( 
select * from Areas 
through the parent node) WHERE id = 7--The child ID UNION all 
SELECT areas.* from Areas to query, 
where tree. Id = areas.pid
) SELECT area from tree 

Querying the parent node from a child node results in:

Modify code to

--Querying the parent node
declare @area varchar (8000) through a child node;
With the tree 
as (SELECT * from Areas 
WHERE id = 6--The child ID UNION all SELECT areas.* to query from 
Areas, T REE 
WHERE Tree. PId = areas.id
)
Select @area =isnull (@area, ') +area from the tree order by Id 
Select Area= @area

The result is: Fengtai District, Peking City, China

This code can be encapsulated as a stored procedure based on the above

-----stored procedure, recursively gets the tree region table string
if exists (select * from sysobjects where name= ' sp_getareastr ')
drop proc sp_getareastr< C3/>go
CREATE PROCEDURE sp_getareastr 
@id int
as
declare @area varchar (8000)
begin
with Tree as 
( 
SELECT * from Areas 
WHERE id = @id--The child ID UNION all 
SELECT areas.* from Areas to query, TRE E 
WHERE Tree. PId = areas.id
)
Select @area =isnull (@area, ') +area from the tree order by Id 
Select Area= @area
end
   go
--exec sp_helptext ' sp_getareastr '
--go
exec sp_getareastr go

Query result: Lingbi County, Suzhou, Anhui Province, China

Table structure used:

Partial data:

The above is a small set to introduce the SQL Server tree table non-circular recursive query examples of the relevant knowledge, I hope to help you, if you have any questions welcome to my message, small series will promptly reply to everyone!