递归遍历一棵树 怎么把符合条件的 数据保存在一张临时表中 或者表变量中
我用的是全局临时表 不知道大家有没有其他更好的方法？
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 [email protected] 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 [email protected] 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
)