SQL Server 查询树结构的表，查询一个节点的所有子节点

[NotNull]

数据库设计如下

Create Table Tab 
(
    Id int ,
	PId int
)
INSERT INTO dbo.Tab
        ( Id, PId )
SELECT 1,NULL
UNION
SELECT 2,NULL
UNION  
SELECT 3,1
UNION  
SELECT 4,1
UNION
SELECT 5,1
UNION  
SELECT 6,3
UNION 
SELECT 7,2

SELECT * FROM dbo.Tab

下面的数据不固定，不一定有多少个子节点
求助，如何查询节点1之下的所有节点

[--小F--]

;with f as 
(
select * from tab where id=1
union all
select a.* from tab as a inner join f as b on a.pid=b.id
)

select * from f

[直面人生]

引用 1 楼 fredrickhu 的回复:

;with f as 
(
select * from tab where id=1
union all
select a.* from tab as a inner join f as b on a.pid=b.id
)

select * from f

向版主这样用CTE递归就搞定了

[xxfvba]

with cte as (select id,pid from Tab where pid=1 union all select t.id,a.pid from cte a,Tab t where t.pid=a.id) select * from cte

[xxfvba]

哈哈,写的都一样啊

[直面人生]

引用 4 楼 xxfvba 的回复:

哈哈,写的都一样啊

是阿,懒得写了 直接引用

[sedshy]

with ct (ChildID, ParentID, [Level]) as (
	select ChildID, ParentID, 1 as [Level] from LinkTable where ParentID = 284
	union all
	select  e.ChildID, e.ParentID, [Level] + 1 from ct t, LinkTable e
	where t.ChildID = e.ParentID
)
select * from ct

[LongRui888]

可以参考这个： 在论坛中出现的比较难的sql问题：21(递归问题3) http://blog.csdn.net/sqlserverdiscovery/article/details/18363633