树结构

CREATE PROCEDURE TreeTmWL @ItemID varchar(50) as begin declare @m varchar(20) declare @t int set @t=1 set @m='' declare @layer int set @layer=1 select *, @layer as layer,cast('01' as varchar(20)) as layerID into #t from WLTmDbfD where D=@ItemID union all select *,@layer-1,'01' from WLTmDbfD where F=@ItemID while exists(select * from WLTmDbfD where D in (select F from #t where layer=@layer)) begin while @t<=@layer+1 begin set @m=@m+'0'+cast(@t as varchar(10)) set @t=@t+1 end insert #t select *,@layer+1,+@m from WLTmDbfD where D in (select F from #t where layer=@layer) set @layer=@layer+1 set @t=1 set @m='' end select rtrim(A) as A,rtrim(B) AS B,rtrim(C) AS C,rtrim(D) AS D,rtrim(F) AS F,rtrim(FHBS) AS FHBS,rtrim(LAYER) AS LAYER,rtrim(LAYERID) as layerid from #t ORDER BY LAYER end GO