递归查询

（递归查询）

 

步骤： 1：建一个经典的递归表结构

 

          2：用 with as 来对表进行递归查询

 

 

create table Tree
(
    NodeId int ,
    ParentId int,
    NodeName nvarchar(50)
)  

insert Tree(NodeId, ParentID, NodeName) values (0,-1,'全球')

insert Tree(NodeId, ParentID, NodeName) values (1,0,'美国')
insert Tree(NodeId, ParentID, NodeName) values (2,0,'中国')
insert Tree(NodeId, ParentID, NodeName) values (3,0,'德国')

insert Tree(NodeId, ParentID, NodeName) values (4,2,'四川省')
insert Tree(NodeId, ParentID, NodeName) values (5,2,'广东省')
insert Tree(NodeId, ParentID, NodeName) values (6,2,'山东省')

insert Tree(NodeId, ParentID, NodeName) values (7,4,'成都市')
insert Tree(NodeId, ParentID, NodeName) values (8,4,'泸州市')
insert Tree(NodeId, ParentID, NodeName) values (9,4,'乐山市')

insert Tree(NodeId, ParentID, NodeName) values (10,8,'纳溪区')
insert Tree(NodeId, ParentID, NodeName) values (11,8,'江阳区')
insert Tree(NodeId, ParentID, NodeName) values (12,8,'龙马潭')

insert Tree(NodeId, ParentID, NodeName) values (13,10,'护国镇')
insert Tree(NodeId, ParentID, NodeName) values (14,10,'合面镇')
insert Tree(NodeId, ParentID, NodeName) values (15,10,'丰乐镇')

insert Tree(NodeId, ParentID, NodeName) values (16,13,'大营村')
insert Tree(NodeId, ParentID, NodeName) values (17,13,'沙田村')
insert Tree(NodeId, ParentID, NodeName) values (18,13,'东巷口')

WITH OrderList
AS
(
select * from Tree
where NodeName='纳溪区'

union all

SELECT t.*
from Tree as t,OrderList as ol
where ol.ParentID = t.NodeId  /*递归查询*/
)
select * from OrderList
OPTION (MAXRECURSION 10);



with temptree as
(
    select * from tree where NodeName = '全球'
    union all
    select t.NodeId, t.ParentId, t.NodeName 
    from tree as t , temptree as tt
    where t.ParentID = tt.NodeId
)
select tt.* from tree as t , temptree as tt 
where t.NodeId = tt.parentID