求助:递归查询难题?

wtydoone 2006-02-23 05:11:06
表名称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





但是我水平有限制达不到返回“由类似 “父目录名称\上级目录名称\目录名称” 这个种形式组成”

结果效果。
大家帮帮忙呀!





...全文
92 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wtydoone 2006-02-24
谢谢以上两位高人,在你们指导下,我的问题解决非常感谢你们。
再次感谢大家对我的问题关注!!!
回复
caiyunxia 2006-02-23
转邹建的
-- 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
go

WITH 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
GO

drop 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)
--*/

回复
wgsasd311 2006-02-23
set nocount on
create table tb(dirid int,dirname varchar(20),pdirid int)
insert tb
select 122,'jkljkljkljkl',0 union all
select 123,'hjkghjkgh',122 union all
select 124,'436545' , 122 union all
select 125,'ftrhyre' , 124 union all
select 126,'t5uy54' , 124 union all
select 127,'truytyu' , 124 union all
select 128,'gjmmtr', 124 union all
select 129,'435' , 124 union all
select 130,'ret', 129 union all
select 132,'567YTU' , 129 union all
select 133,'GFHJ' , 129 union all
select 134,'HGJ', 132 union all
select 135,'GFJ' , 132 union all
select 136,',MBVMJHVJ',134
go
create function f_str(@dirid int)
returns varchar(8000)
as
begin
declare @i int,@re varchar(8000)
declare @tb table (id int ,dirid int,dirname varchar(20),pdirid int)
set @re=''
set @i=1
insert into @tb
select @i,dirid,dirname,pdirid from tb where dirid=@dirid
while @@rowcount>0
begin
set @i=@i+1
insert into @tb
select @i,b.dirid,b.dirname,b.pdirid
from @tb a,tb b where a.pdirid=b.dirid and a.id=@i-1
end

select @re=@re+'\'+dirname from @tb order by id DESC
return stuff(@re,1,1,'')
end
go
select dbo.f_str(dirid) from tb
go
drop table tb
drop function dbo.f_str
回复
$扫地僧$ 2006-02-23
create table T_DB_DirMgt
(
DirID int,
DirName varchar(100),
pDirID int
)

insert T_DB_DirMgt select 122,'jkljkljkljkl',0 ---代表根目录
insert T_DB_DirMgt select 123,'hjkghjkgh',122
insert T_DB_DirMgt select 124,'436545',122
insert T_DB_DirMgt select 125,'ftrhyre',124
insert T_DB_DirMgt select 126,'t5uy54',124
insert T_DB_DirMgt select 127,'truytyu',124
insert T_DB_DirMgt select 128,'gjmmtr',124
insert T_DB_DirMgt select 129,'435',124
insert T_DB_DirMgt select 130,'ret',129
insert T_DB_DirMgt select 132,'567YTU',129
insert T_DB_DirMgt select 133,'GFHJ',129
insert T_DB_DirMgt select 134,'HGJ',132
insert T_DB_DirMgt select 135,'GFJ',132
insert T_DB_DirMgt select 136,',MBVMJHVJ',134


drop Function FC_SYS_DirMgt_UpSubTree
create Function FC_SYS_DirMgt_UpSubTree (@T_id int)
returns varchar(8000)
as
begin
declare @T_SQL varchar(8000)
declare @T1_SQL varchar(8000)
declare @T table(DirID int, DirName varchar(100), pDirID int,lev int,T varchar(8000))
declare @lev int
set @lev=1
select @T_SQL=DirName from T_DB_DirMgt where DirID=@T_id
insert @T select DirID,DirName,pDirID,@lev ,@T_SQL as T from T_DB_DirMgt where DirID=@T_id
while @@rowcount>0
begin
set @lev=@lev+1
select @T1_SQL=DirName from T_DB_DirMgt where DirID in (select pDirID from @T where lev=@lev-1)
set @T_SQL=@T1_SQL+'\'+ @T_SQL
insert @T select DirID,DirName,pDirID,@lev,@T_SQL from T_DB_DirMgt where DirID in (select pDirID from @T where lev=@lev-1)
end
select top 1 @T_SQL=T from @T order by lev DESC
return @T_SQL
end

select *, dbo.FC_SYS_DirMgt_UpSubTree(DirID) from T_DB_DirMgt
回复
子陌红尘 2006-02-23
楼上的方法用两个用户定义函数,一个用于获取某个节点及其所有的子节点;另一个用于获取某个节点相对根目录的路径。
回复
子陌红尘 2006-02-23
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
go

create 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
go

create 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
go

select pDirID,dbo.f_str(pDirID) DirName from dbo.f_getchild(129) order by DirName

drop 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
*/
回复
wgsasd311 2006-02-23
set nocount on
create table tb(dirid int,dirname varchar(20),pdirid int)
insert tb
select 122,'jkljkljkljkl',0 union all
select 123,'hjkghjkgh',122 union all
select 124,'436545' , 122 union all
select 125,'ftrhyre' , 124 union all
select 126,'t5uy54' , 124 union all
select 127,'truytyu' , 124 union all
select 128,'gjmmtr', 124 union all
select 129,'435' , 124 union all
select 130,'ret', 129 union all
select 132,'567YTU' , 129 union all
select 133,'GFHJ' , 129 union all
select 134,'HGJ', 132 union all
select 135,'GFJ' , 132 union all
select 136,',MBVMJHVJ',134
go
create function f_str(@dirid int)
returns varchar(8000)
as
begin
declare @i int,@re varchar(8000)
declare @tb table (id int ,dirid int,dirname varchar(20),pdirid int)
set @re=''
set @i=1
insert into @tb
select @i,dirid,dirname,pdirid from tb where dirid=@dirid
while @@rowcount>0
begin
set @i=@i+1
insert into @tb
select @i,b.dirid,b.dirname,b.pdirid
from @tb a,tb b where a.pdirid=b.dirid and a.id=@i-1
end

select @re=@re+'\'+dirname from @tb order by id
return stuff(@re,1,1,'')
end
go
select dbo.f_str(dirid) from tb
go
drop table tb
drop function dbo.f_str
回复
zhy16 2006-02-23
兄弟!!咱们碰到一样问题了,不过我比你简单,我只要查出当前结点下所有包含的子结点就成了,有答案别忘了发一份大家研究一下。谢了。
回复
子陌红尘 2006-02-23
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
go

create 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
go

select pDirID,dbo.f_str(pDirID) DirName from BOM

drop 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
*/
回复
wgsasd311 2006-02-23
create function f_str(@dirid int)
returns varchar(8000)
as
begin
declare @i int,@re varchar(8000)
declare @tb table (id int ,dirid int,dirname varchar(20),pdirid int)
set @re=''
set @i=1
insert into @tb
select @i,dirid,dirname,pdirid from tb where dirid=@dirid
while @@fetch_status=0
begin
set @i=@i+1
insert into @tb
select @i,b.dirid,b.dirname,b.pdirid
from @tb a,tb b where a.pdirid=b.dirid and a.id=@i-1
end

select @re=@re+'\'+dirname from @tb order by id
return stuff(@re,1,1,'')
end
go

select dbo.f_str(dirid) from tb
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-02-23 05:11
社区公告
暂无公告