递归遍历一棵树 怎么把符合条件的 数据保存在一张临时表中 或者表变量中
递归遍历一棵树 怎么把符合条件的 数据保存在一张临时表中 或者表变量中

我用的是全局临时表 不知道大家有没有其他更好的方法？

CREATE procedure rp_GetDepItemDetailID_Centaline
(
@FItemID int
)
as


SET NOCOUNT ON -------------------------设置 取消影响行数

declare MyCursor cursor scroll local for
Select FItemID, FDetail from t_item where FParentID =@FItemID and FDeleted = 0

open MyCursor

declare @FItemIDTemp sysname
declare @FDetailTemp sysname


fetch next from MyCursor into @FItemIDTemp, @FDetailTemp

while(@@fetch_status=0)
begin
if @FDetailTemp = 0
Exec rp_GetDepItemDetailID_Centaline @FItemIDTemp
else
insert into ##ItemDetailID values(@FItemIDTemp)

fetch next from MyCursor into @FItemIDTemp, @FDetailTemp
end

close MyCursor

deallocate MyCursor
GO

[解决办法]
不用游标,你好像只要叶子节点

CREATE procedure rp_GetDepItemDetailID_Centaline
(
@FItemID int
)
as


SET NOCOUNT ON -------------------------设置 取消影响行数

--存放结果
/*
declare @r table (
FItemID int,
FDetail varchar(20)
)
*/

--存放中间节点
declare @t table (
FParentID int,
FItemID int,
FDetail varchar(20)
)

insert @t
Select FParentID,FItemID, FDetail from t_item where FParentID =@FItemID and FDeleted = 0

while exists (
Select FParentID,FItemID, FDetail from t_item where FParentID in (select FItemID from @t)
and FItemID not in (select FItemID from @t)
and FDeleted = 0
)

insert @t
Select FParentID,FItemID, FDetail from t_item where FParentID in (select FItemID from @t)
and FItemID not in (select FItemID from @t)
and FDeleted = 0

--存放结果
--insert @r
select * from @t a
where not exists (
select 1 from @t
where FParentID=a.FItemID
)