ctes.txt

SQL Server 2005中一个CTEs的小示例

Use AdventureWorks
if exists(select [name] from sys.tables where [name]='p_c')
      drop table p_c
create table p_c (id int,parentID int)

insert into p_c values(0,null)
--切忌使用0作为作为根节点的父节点引用值
--否则会引起逆向枚举时的死循环而导致超出CTE的自嵌套层数限制
insert into p_c values (1,0)
insert into p_c values (2,0)
insert into p_c values (3,1)
insert into p_c values (4,2)
insert into p_c values (5,3)
insert into p_c values (6,0)
insert into p_c values (7,4)
insert into p_c values (8,5)
insert into p_c values (9,8)
insert into p_c values (10,4)
insert into p_c values (11,10)
insert into p_c values (12,2)
insert into p_c values (13,3)
insert into p_c values (14,6)
insert into p_c values (15,14)
insert into p_c values (16,11)
insert into p_c values (17,14)
insert into p_c values (18,15)
insert into p_c values (19,18)
insert into p_c values (20,7)
insert into p_c values (21,9)
insert into p_c values (22,9)
insert into p_c values (23,12)
insert into p_c values (24,13)
insert into p_c values (25,12)
insert into p_c values (26,15)

--cte的代码实现，仅检索节点5的所有子节点
with pcv(p,c,L) as
(
select p.id,p.parentid,0
from p_c as p where p.parentid=5
union all
select c.id,c.parentid,L+1
from p_c as c inner join pcv on c.parentid=pcv.p
)
select * from pcv
go

--利用存储过程实现任意节点子节点的检索
create procedure dbo.usp_GetChildrenHierarchy
@ParentID int
as
with pcv(p,c,L) as
(
select P.ID,P.ParentID,0--初始的层次计数
from p_c as p where P.ParentID=@ParentID
union all
select C.ID,C.ParentID ,L+1
from P_C as c inner join PCV on C.ParentID=pcv.p
)
select * from pcv
go


exec dbo.usp_GetChildrenHierarchy 3
exec dbo.usp_GetChildrenHierarchy 5
exec dbo.usp_GetChildrenHierarchy 8
exec dbo.usp_GetChildrenHierarchy 9

--利用存储过程实现任意节点父节点的检索
create procedure dbo.usp_GetParentHierarchy
@ChildrenID int
as
with pcv(c,p,L) as
(
select P.ID,P.ParentID,0--初始的层次计数
from p_c as p where P.ID=@ChildrenID
union all
select C.ID,C.ParentID ,L-1
from P_C as c inner join PCV on C.ID=pcv.p
)
select * from pcv
go


exec dbo.usp_GetParentHierarchy 3
exec dbo.usp_GetParentHierarchy 5
exec dbo.usp_GetParentHierarchy 8
exec dbo.usp_GetParentHierarchy 9

drop procedure dbo.usp_GetChildrenHierarchy
drop procedure dbo.usp_GetParentHierarchy
drop table p_c