SQL２００５下使用CTE的一个示例

create   table  Tree(nId  smallint , NodeName  nvarchar ( 16 ), pId  smallint )
insert  Tree  select   1 ,  ' 中国 ' ,  NULL
union   all   select   2 ,  ' 北京 ' ,  1
union   all   select   3 ,  ' 上海 ' ,  1
union   all   select   4 ,  ' 湖北 ' ,  1
union   all   select   5 ,  ' 湖南 ' ,  1
union   all   select   6 ,  ' 武汉 ' ,  4
union   all   select   7 ,  ' 孝感 ' ,  4
union   all   select   8 ,  ' 长沙 ' ,  5
union   all   select   9 ,  ' 岳阳 ' ,  5
union   all   select   10 ,  ' 海淀区 ' ,  2
union   all   select   11 ,  ' 朝阳区 ' ,  2
union   all   select   12 ,  ' 上地 ' ,  10
union   all   select   13 ,  ' 西三旗 ' ,  10

go
create   procedure  sp_GetNoedsByRootID
     @nId   smallint
as
begin     
     with  NewTree(nId, NodeName, pId)  as
    (
         select   *   from  Tree  where  nId = @nId
         union   all
         select  T. *   from  Tree T  join  NewTree NT  on  T.pId = NT.nId
    )
     select  NT.nId, NT.NodeName, PT.NodeName  as   [ ParentName ]
     from  NewTree NT  join  Tree PT  on  NT.pId = PT.nId            
end
go

create   procedure  sp_DelNoedsByRootID
     @nId   smallint
as
begin
     declare   @sql   nvarchar ( max )
     set   @sql = '
                with NewTree(nId, NodeName, pId) as
                (
                    select * from Tree where nId=@nId
                    union all
                    select T.* from Tree T join NewTree NT on T.pId=NT.nId
                )
                delete Tree 
                from Tree T join NewTree NT on T.nId=NT.nId
             '
     exec  sp_executesql  @sql , N ' @nId smallint ' ,  @nId
end
go

-- 查找节点及子节点
exec  sp_GetNoedsByRootID  2

-- 删除节点及子节点
exec  sp_DelNoedsByRootID  2
select   *   from  Tree

drop   table  Tree
drop   procedure  sp_GetNoedsByRootID, sp_DelNoedsByRootID

 在SQL2000下实现类似功能的代码：

 

-- 测试数据
CREATE   TABLE  tb(ID  char ( 3 ),PID  char ( 3 ),Name  nvarchar ( 10 ))
INSERT  tb  SELECT   ' 001 ' , NULL  , ' 山东省 '
UNION   ALL   SELECT   ' 002 ' , ' 001 ' , ' 烟台市 '
UNION   ALL   SELECT   ' 004 ' , ' 002 ' , ' 招远市 '
UNION   ALL   SELECT   ' 003 ' , ' 001 ' , ' 青岛市 '
UNION   ALL   SELECT   ' 005 ' , NULL  , ' 四会市 '
UNION   ALL   SELECT   ' 006 ' , ' 005 ' , ' 清远市 '
UNION   ALL   SELECT   ' 007 ' , ' 006 ' , ' 小分市 '
GO

-- 查询指定节点及其所有子节点的函数
CREATE   FUNCTION  f_Cid( @ID   char ( 3 ))
RETURNS   @t_Level   TABLE (ID  char ( 3 ), Level   int )
AS
BEGIN
     DECLARE   @Level   int
     SET   @Level = 1
     INSERT   @t_Level   SELECT   @ID , @Level
     WHILE   @@ROWCOUNT > 0
     BEGIN
         SET   @Level = @Level + 1
         INSERT   @t_Level   SELECT  a.ID, @Level
         FROM  tb a, @t_Level  b
         WHERE  a.PID = b.ID
             AND  b. Level = @Level - 1
     END
     RETURN
END
GO

-- 调用函数查询002及其所有子节点
SELECT  a. *
FROM  tb a,f_Cid( ' 002 ' ) b
WHERE  a.ID = b.ID
/**/ /*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/