求助：递归查询难题？

Question

表名称T_DB_DirMgt 
   字段名 ： pDirID     int      ----  上级目录ID
              DirID     int      -----  目录ID
              DirName   varchar(100)  ------ 目录名称 
 表名称T_DB_DirMgt 的数据如下：               
目录id  目录名称        上级目录ID
122 jkljkljkljkl 0            ---代表根目录
123 hjkghjkgh 122
124 436545         122
125 ftrhyre         124
126 t5uy54         124
127 truytyu         124
128 gjmmtr         124
129 435         124
130 ret         129
132 567YTU         129
133 GFHJ         129
134 HGJ         132
135 GFJ         132
136 ,MBVMJHVJ 134
我想达到以下效果：
 目录名称 :由类似 “父目录名称\上级目录名称\目录名称” 这个种形式组成。
jkljkljkljkl
jkljkljkljkl\hjkghjkgh
jkljkljkljkl\436545
jkljkljkljkl\436545\ftrhyre
         jkljkljkljkl\436545\t5uy54
jkljkljkljkl\436545\truytyu
jkljkljkljkl\436545\gjmmtr
jkljkljkljkl\436545\435
jkljkljkljkl\436545\435\ret
jkljkljkljkl\436545\435\567YTU
jkljkljkljkl\436545\435\GFHJ
jkljkljkljkl\436545\435\567YTU\HGJ
jkljkljkljkl\436545\435\567YTU\GFJ
jkljkljkljkl\436545\435\567YTU\HGJ\,MBVMJHVJ
要怎么样来写一个过程函数，或存储过程，返回“由类似 “父目录名称\上级目录名称\目录名称” 这个种形式组成”结果
   我试用递归查询，如下：
  /******************************************************************/
  CREATE FUNCTION dbo.FC_SYS_DirMgt_UpSubTree(
  /*******************************************************************************************
**********************
  * 功能：取得当前目录所有子目录
  * 参数：@DirID------目录ID  
  * 返回：含有取得当前目录所有详细从父目录到子目录的一张表
  * 作者：wuty
  
********************************************************************************************
********************/
  @DirID AS int
) 
RETURNS @treeinfo table( 
  [DirID] int NOT NULL, 
  [DirName] varchar(500) NOT NULL,
  [pDirID] int NOT NULL,
  [level] int NOT NULL
) AS
BEGIN 
    DECLARE @level AS int
    SELECT @level = 0
    If @DirID = '0'
       INSERT INTO @treeinfo
         SELECT [DirID],[DirName],[pDirID],@level
            FROM [T_DB_DirMgt] 
               WHERE pDirID = @DirID
    else
       INSERT INTO @treeinfo
         SELECT [DirID],[DirName],[pDirID],@level
           FROM [T_DB_DirMgt] 
               WHERE DirID = @DirID
  WHILE @@ROWCOUNT > 0 
  BEGIN 
     SET @level = @level + 1 
       
       INSERT INTO @treeinfo 
         SELECT E.[DirID],T.[DirName]+'\'+E.[DirName],E.[pDirID],@level
           FROM [T_DB_DirMgt] AS E JOIN @treeinfo AS T 
             ON E.[pDirID] = T.[DirID] AND T.[level] = @level - 1   
 END 
     
  RETURN 
END
 /**********************************************************************/ 
  以上若取select * from dbo.FC_SYS_DirMgt_UpSubTree('122')
    可以达到以上效果，
         jkljkljkljkl
jkljkljkljkl\hjkghjkgh
jkljkljkljkl\436545
jkljkljkljkl\436545\ftrhyre
         jkljkljkljkl\436545\t5uy54
jkljkljkljkl\436545\truytyu
jkljkljkljkl\436545\gjmmtr
jkljkljkljkl\436545\435
jkljkljkljkl\436545\435\ret
jkljkljkljkl\436545\435\567YTU
jkljkljkljkl\436545\435\GFHJ
jkljkljkljkl\436545\435\567YTU\HGJ
jkljkljkljkl\436545\435\567YTU\GFJ
jkljkljkljkl\436545\435\567YTU\HGJ\,MBVMJHVJ  但是我若执行select * from dbo.FC_SYS_DirMgt_UpSubTree('129')结果如下：
目录id  目录名称                     上级目录ID
129 435                          124                 
130 435\ret                         129                 
132 435\567YTU                 129                
133 435\GFHJ                 129                
134 435\567YTU\HGJ                 132                
135 435\567YTU\GFJ                 132                
136 435\567YTU\HGJ\,MBVMJHVJ 134 ====================
达不到以下父目录名称\上级目录名称\目录名称效果：
          jkljkljkljkl\436545\435
jkljkljkljkl\436545\435\ret
jkljkljkljkl\436545\435\567YTU
jkljkljkljkl\436545\435\GFHJ
jkljkljkljkl\436545\435\567YTU\HGJ
jkljkljkljkl\436545\435\567YTU\GFJ
jkljkljkljkl\436545\435\567YTU\HGJ\,MBVMJHVJ
       
     但是我水平有限制达不到返回“由类似 “父目录名称\上级目录名称\目录名称” 这个种形式组成”结果效果。
大家帮帮忙呀！
 

Answer 1

create table BOM(pDirID int,DirName varchar(100),DirID int)
insert into BOM select 122,rtrim('jkljkljkljkl'),0
insert into BOM select 123,rtrim('hjkghjkgh')   ,122
insert into BOM select 124,rtrim('436545')      ,122
insert into BOM select 125,rtrim('ftrhyre')     ,124
insert into BOM select 126,rtrim('t5uy54')      ,124
insert into BOM select 127,rtrim('truytyu')     ,124
insert into BOM select 128,rtrim('gjmmtr')      ,124
insert into BOM select 129,rtrim('435')         ,124
insert into BOM select 130,rtrim('ret')         ,129
insert into BOM select 132,rtrim('567YTU')      ,129
insert into BOM select 133,rtrim('GFHJ')        ,129
insert into BOM select 134,rtrim('HGJ')         ,132
insert into BOM select 135,rtrim('GFJ')         ,132
insert into BOM select 136,rtrim(',MBVMJHVJ')   ,134
gocreate function f_str(@pDirID int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000),@DirID int
    select @DirID=DirID,@ret=DirName from BOM where pDirID=@pDirID
    while @@rowcount<>0
    begin
        set @pDirID=@DirID
        select @DirID=DirID,@ret=DirName+'/'+@ret from BOM where pDirID=@pDirID
    end
    return @ret
end
goselect pDirID,dbo.f_str(pDirID) DirName from BOMdrop function f_str
drop table BOM/*
pDirID      DirName
----------- --------------------------------------------------
122         jkljkljkljkl
123         jkljkljkljkl/hjkghjkgh
124         jkljkljkljkl/436545
125         jkljkljkljkl/436545/ftrhyre
126         jkljkljkljkl/436545/t5uy54
127         jkljkljkljkl/436545/truytyu
128         jkljkljkljkl/436545/gjmmtr
129         jkljkljkljkl/436545/435
130         jkljkljkljkl/436545/435/ret
132         jkljkljkljkl/436545/435/567YTU
133         jkljkljkljkl/436545/435/GFHJ
134         jkljkljkljkl/436545/435/567YTU/HGJ
135         jkljkljkljkl/436545/435/567YTU/GFJ
136         jkljkljkljkl/436545/435/567YTU/HGJ/,MBVMJHVJ
*/

Answer 2

create table BOM(pDirID int,DirName varchar(100),DirID int)
insert into BOM select 122,rtrim('jkljkljkljkl'),0
insert into BOM select 123,rtrim('hjkghjkgh')   ,122
insert into BOM select 124,rtrim('436545')      ,122
insert into BOM select 125,rtrim('ftrhyre')     ,124
insert into BOM select 126,rtrim('t5uy54')      ,124
insert into BOM select 127,rtrim('truytyu')     ,124
insert into BOM select 128,rtrim('gjmmtr')      ,124
insert into BOM select 129,rtrim('435')         ,124
insert into BOM select 130,rtrim('ret')         ,129
insert into BOM select 132,rtrim('567YTU')      ,129
insert into BOM select 133,rtrim('GFHJ')        ,129
insert into BOM select 134,rtrim('HGJ')         ,132
insert into BOM select 135,rtrim('GFJ')         ,132
insert into BOM select 136,rtrim(',MBVMJHVJ')   ,134
gocreate function f_str(@pDirID int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000),@DirID int
    select @DirID=DirID,@ret=DirName from BOM where pDirID=@pDirID
    while @@rowcount<>0
    begin
        set @pDirID=@DirID
        select @DirID=DirID,@ret=DirName+'/'+@ret from BOM where pDirID=@pDirID
    end
    return @ret
end
gocreate function f_getchild(@pDirID int)
returns @t table(pDirID int)
as
begin
    insert into @t select pDirID from BOM where pDirID=@pDirID
    while @@rowcount<>0
    begin
        insert into @t
        select 
            a.pDirID
        from 
            BOM a,@t b 
        where 
            a.DirID=b.pDirID
            and
            not exists(select 1 from @t where pDirID=a.pDirID)
    end
    return
end
goselect pDirID,dbo.f_str(pDirID) DirName from dbo.f_getchild(129) order by DirNamedrop function f_str,f_getchild
drop table BOM/*
pDirID      DirName
----------- --------------------------------------------------
129         jkljkljkljkl/436545/435
132         jkljkljkljkl/436545/435/567YTU
135         jkljkljkljkl/436545/435/567YTU/GFJ
134         jkljkljkljkl/436545/435/567YTU/HGJ
136         jkljkljkljkl/436545/435/567YTU/HGJ/,MBVMJHVJ
133         jkljkljkljkl/436545/435/GFHJ
130         jkljkljkljkl/436545/435/ret
*/

Answer 3

-- sql 2005
create table BOM(pDirID int,DirName varchar(100),DirID int)
insert into BOM select 122,rtrim('jkljkljkljkl'),0
insert into BOM select 123,rtrim('hjkghjkgh')   ,122
insert into BOM select 124,rtrim('436545')      ,122
insert into BOM select 125,rtrim('ftrhyre')     ,124
insert into BOM select 126,rtrim('t5uy54')      ,124
insert into BOM select 127,rtrim('truytyu')     ,124
insert into BOM select 128,rtrim('gjmmtr')      ,124
insert into BOM select 129,rtrim('435')         ,124
insert into BOM select 130,rtrim('ret')         ,129
insert into BOM select 132,rtrim('567YTU')      ,129
insert into BOM select 133,rtrim('GFHJ')        ,129
insert into BOM select 134,rtrim('HGJ')         ,132
insert into BOM select 135,rtrim('GFJ')         ,132
insert into BOM select 136,rtrim(',MBVMJHVJ')   ,134
goWITH tree(pDirID, DirName)
AS(
SELECT pDirID, DirName = CAST(DirName as varchar(max))
FROM BOM A
WHERE DirID = 0
UNION ALL
SELECT A.pDirID, DirName = B.DirName + '/' + A.DirName
FROM BOM A, tree B
WHERE A.DirID = B.pDirID
)
SELECT * FROM tree
ORDER BY DirName
GOdrop table BOM/*-- 结果
pDirID      DirName
----------- ---------------------------------------------------
122         jkljkljkljkl
124         jkljkljkljkl/436545
129         jkljkljkljkl/436545/435
132         jkljkljkljkl/436545/435/567YTU
135         jkljkljkljkl/436545/435/567YTU/GFJ
134         jkljkljkljkl/436545/435/567YTU/HGJ
136         jkljkljkljkl/436545/435/567YTU/HGJ/,MBVMJHVJ
133         jkljkljkljkl/436545/435/GFHJ
130         jkljkljkljkl/436545/435/ret
125         jkljkljkljkl/436545/ftrhyre
128         jkljkljkljkl/436545/gjmmtr
126         jkljkljkljkl/436545/t5uy54
127         jkljkljkljkl/436545/truytyu
123         jkljkljkljkl/hjkghjkgh(14 row(s) affected)
--*/

Answer 4

-- sql 2005
create table BOM(pDirID int,DirName varchar(100),DirID int)
insert into BOM select 122,rtrim('jkljkljkljkl'),0
insert into BOM select 123,rtrim('hjkghjkgh')   ,122
insert into BOM select 124,rtrim('436545')      ,122
insert into BOM select 125,rtrim('ftrhyre')     ,124
insert into BOM select 126,rtrim('t5uy54')      ,124
insert into BOM select 127,rtrim('truytyu')     ,124
insert into BOM select 128,rtrim('gjmmtr')      ,124
insert into BOM select 129,rtrim('435')         ,124
insert into BOM select 130,rtrim('ret')         ,129
insert into BOM select 132,rtrim('567YTU')      ,129
insert into BOM select 133,rtrim('GFHJ')        ,129
insert into BOM select 134,rtrim('HGJ')         ,132
insert into BOM select 135,rtrim('GFJ')         ,132
insert into BOM select 136,rtrim(',MBVMJHVJ')   ,134
goWITH tree(pDirID, DirName, DirID, Level)
AS(
SELECT pDirID, DirName = CAST(DirName as varchar(max)), DirID, 0
FROM BOM A
WHERE NOT EXISTS(
SELECT * FROM BOM
WHERE DirID = A.pDirID)
UNION ALL
SELECT B.pDirID, DirName = A.DirName + '/' + B.DirName, A.DirID, B.Level + 1
FROM BOM A, tree B
WHERE A.pDirID = B.DirID
)
SELECT pDirID, DirName
FROM tree A
WHERE DirID = 0
ORDER BY pDirID
GOdrop table BOM/*-- 结果
pDirID      DirName
----------- ----------------------------------------------
123         jkljkljkljkl/hjkghjkgh
125         jkljkljkljkl/436545/ftrhyre
126         jkljkljkljkl/436545/t5uy54
127         jkljkljkljkl/436545/truytyu
128         jkljkljkljkl/436545/gjmmtr
130         jkljkljkljkl/436545/435/ret
133         jkljkljkljkl/436545/435/GFHJ
135         jkljkljkljkl/436545/435/567YTU/GFJ
136         jkljkljkljkl/436545/435/567YTU/HGJ/,MBVMJHVJ(9 row(s) affected)
--*/