求助：递归查询难题？

2006-02-23 05:11:06 179 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Windows服务器渗透必备：5款杀软在线识别平台实测对比（附避坑指南）本文针对Windows服务器渗透测试中的关键环节，实测对比了五款主流的在线杀软识别平台。文章从实战场景出发，深入分析了各平台在识别准确率、响应速度、隐匿性及使用风险上的差异，并提供了详尽的避坑指南与高级隐匿技巧，旨在帮助安全人员快速、精准地识别目标环境安全防护，为后续行动奠定情报基础。阅读详情

表名称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

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

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

...全文

app逆向分析案例-95分先把justtrusmeplus打开设置好95分，开始抓包，得到结果为： https://www.95fenapp.com/api_goods/list/v3.0?is_all=1&keywords=%E7%A7%91%E6%AF%94& page=1&page_size=20 &publish_timestamp=0 &scene=2&scene_type=95fen_android_search_personal&sn=SearchList 阅读详情

wtydoone

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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

求助：递归查询难题？

相关推荐