如何用一段SQL语句递归找到一个目录下的所有子目录
发布网友
发布时间：2022-04-08 05:47
共1个回答
热心网友
时间：2022-04-08 07:16
如下:(ClassID表示该目录的编号,ClassParentID表示它的父目录的编号)
ClassID ClassParentID
1 1
2 1
3 2
4 3
5 3
表示的关系应该是
-1--(根目录)
|
-2--(第二级目录)
|
-3--(第*目录)
|
-4--(第四级目录)
|
-5--(第四级目录)
这样的目录关系.
那么我通过知道一个目录的ClassID,如何通过一条SQL语句找到所有该目录的子目录?
如:
"SELECT * FROM 表 WHERE ClassParentID = "+ClassID+"and ClassParentID <>"+ClassID
但只能找到第一层的子目录,如何找到所有子目录,请大家给一条SQL语句实现!!
(因为事先不知道目录有多少层,当然一定是一条带有while循环的SQL语句)
1、
create table Test2
(
ClassID int,
ClassParentID int,
)
go
create proc Test2_proc
@nClassParentID int -- 要查询的父节点ID 号
as
begin
set nocount on
--
declare @nClassID int,
@nIndex int
-- create TempTable
create table #ClassIDTemp
(
nIndex int identity(1,1),
ClassID int,
ClassParentID int,
nFlag int default(0),
)
create index #ClassIDTemp_Index on #ClassIDTemp(nIndex)
-- insert First RecordSet
insert into #ClassIDTemp(ClassID,ClassParentID) select
ClassID,ClassParentID from Test2 where ClassParentID =
@nClassParentID order by ClassParentID
-- Circle
while(0=0)
begin
select top 1 @nIndex = nIndex, @nClassID = ClassID
from #ClassIDTemp where nFlag = 0 order by nIndex
asc
if @@rowcount = 0
break
else
begin
insert into #ClassIDTemp(ClassID,ClassParentID) select
ClassID,ClassParentID from Test2 where ClassParentID =
@nClassID order by ClassParentID
end
update #ClassIDTemp set nFlag = 1 where nIndex = @nIndex
end
-- Select Result
select ClassID,ClassParentID from #ClassIDTemp order by nIndex
if exists(select * from sysobjects where name = '#ClassIDTemp')
drop table #ClassIDTemp
end
go
2、
declare @Table table ( ClassID int primary key , ClassParentID int)
declare @Test table ( ClassID int primary key , ClassParentID int)
insert into @Table values ( 1, 0 ) -- 建议这里是 1，0 而不是 1， 1
insert into @Table values ( 2, 1 )
insert into @Table values ( 3, 2 )
insert into @Table values ( 4, 3 )
insert into @Table values ( 5, 3 )
insert into @Test select * from @Table where ClassID = 2
while exists ( select * from @Table A
inner join @Test B
on A.ClassParentID = B.ClassID
where A.ClassID not in ( select ClassID from @Test ) )
begin
insert into @Test
select A.* from @Table A
inner join @Test B
on A.ClassParentID = B.ClassID
where A.ClassID not in ( select ClassID from @Test )
end
select * from @Test
3、
sql1 = "(SELECT ClassID FROM 表 WHERE ClassParentID =" + 变量 + ")"
sql2 = "(SELECT ClassID FROM 表 WHERE (ClassParentID IN
"+ sql1 + ") OR (ClassID IN " + sql1 + "))"
sql = "SELECT * FROM 表 WHERE ClassParentID IN " + sql2 + " OR ClassID IN " + sql2
这个办法可实现1到4级的所有情况（包括只有2级、3级的情况）
4、--我的方法是用自定义函数实现,这样具有通用性
--测试
--测试数据
create table 表(ClassID int,ClassParentID int)
insert 表 select 1,0
union all select 2,1
union all select 3,2
union all select 4,3
union all select 5,3
go
--自定义函数,返回指定目录下的所有子目录列表
create function f_child(@ClassID int)
returns @re table(ClassID int,Level int)
as
begin
declare @l int
set @l=0
insert @re select @ClassID,@l
while @@rowcount>0 --直接用@@rowcount判断
begin
set @l=@l+1
insert @re select a.ClassID,@l
from 表 a join @re b on a.ClassParentID=b.ClassID
where b.level=@l-1
end
return
end
go
--调用函数实现查询
select a.* from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go
--加上缩进效果
select ClassID=space(b.level*4)+cast(a.ClassID as varchar),a.ClassParentID
from 表 a join dbo.f_child(1) b on a.ClassID=b.ClassID
go
--删除测试环境
drop table 表
drop function f_child
/*--测试结果
ClassID ClassParentID
----------- -------------
1 0
2 1
3 2
4 3
5 3
（所影响的行数为 5 行）
ClassID ClassParentID
-------------- ----------------
10
2 1
3 2
4 3
5 3
（所影响的行数为 5 行）
--*/
