SQLSERVER 2005 递归查询

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

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

表数据如下：

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

[sql] view plaincopyprint?

1. --查询子节点
2. with
3. RTD1 as(
4. select id ,pid from UserGroup
5. ),
6. RTD2 as(
7. select * from RTD1where id=6
8. union all
9. select RTD1.* from RTD2 innerjoin RTD1
10. on RTD2.id=RTD1.PID
11. )
12. select * from RTD2

--查询子节点with RTD1 as(select id ,pid from UserGroup),RTD2 as(select * from RTD1 where id=6union allselect 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如下：

[sql] view plaincopyprint?

1. --查询父节点
2. with
3. RTU1 as(
4. select id ,pid from UserGroup
5. ),
6. RTU2 as(
7. select * from RTU1where id=26
8. union all
9. select RTU1.* from RTU2 innerjoin RTU1
10. --on myT2.id=myT.PID
11. on RTU2.PID=RTU1.ID
12. )
13. select * from RTU2

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

查询结果如下：

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

(4 行受影响)