SQLSERVER 2005 递归查询

项目中有用户组表UserGroup如下：

其中PID表示当前组的上级组

表数据如下：

现在想查询出顶级组[没有上级组叫顶级组]A1组的所有子孙组ID,SQL如下：

--查询子节点with 	RTD1 as(		select id ,pid from UserGroup	),	RTD2 as(		select * from RTD1 where id=6		union all		select RTD1.* from RTD2 inner join RTD1 		on RTD2.id=RTD1.PID	)select * from RTD2

查询结果如下：

id????????? pid
----------- -----------
6?????????? NULL
17????????? 6
18????????? 6
20????????? 6
21????????? 20
22????????? 20
23????????? 20
24????????? 20
29????????? 20
25????????? 23
26????????? 23
28????????? 26
27????????? 25

(13 行受影响)

?

现在想查询出A1-B3-C3-D2组的所有上级组ID,SQL如下：

--查询父节点with 	RTU1 as(		select id ,pid from UserGroup	),	RTU2 as(		select * from RTU1 where id=26		union all		select RTU1.* from RTU2 inner join RTU1 		--on myT2.id=myT.PID		on RTU2.PID=RTU1.ID	)select * from RTU2

查询结果如下：

id????????? pid
----------- -----------
26????????? 23
23????????? 20
20????????? 6
6?????????? NULL

(4 行受影响)

?

?

?

?

?

==================================================================

?

--查询某一父节点的所有子节点
with
?RTD1 as(
??select id,name,fid from ProductType
?),
?RTD2 as(
??select id,name,fid from RTD1 where id=10103
??union all
??select RTD1.* from RTD2 inner join RTD1
??on RTD2.id=RTD1.FID
?)
select * from RTD2

?

?

--查询某一父节点的所有子节点
with c as (
???? select * from producTtype where Id =10103
? union all
??? select a.* from producTtype as a
??????? join c on a.fid = c.Id)
?select * FROM? c

?