----bom结构,查找节点下所有子节点:create table os(id int,parentid int,desn varchar(10)) insert into os select 1,0,'体育用品' insert into os select 2,0,'户外运动' insert into os select 3,1,'篮球' insert into os select 4,1,'足球' insert into os select 5,2,'帐篷' insert into os select 6,2,'登山鞋' insert into os select 7,0,'男士用品' insert into os select 8,7,'刮胡刀' insert into os select 9,3,'大号篮球'--求个节点下所有子节点: create function f_cid(@id int) returns varchar(500) as begin declare @t table(id int,parentid int,desn varchar(10),lev int) declare @lev int set @lev=1 insert into @t select *,@lev from os where id=@id while(@@rowcount>0) begin set @lev=@lev+1 insert into @t select a.*,@lev from os a,@t b where a.parentid=b.id and b.lev=@lev-1 end declare @cids varchar(500) select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev return @cids end go--调用函数 select *,ids=dbo.f_cid(id) from os --得到每个节点路径: create proc wsp2 @id int as select *,cast(' ' as varchar(10)) fullpath into #os from os DECLARE @i int,@j int set @i=0 set @j=1 select @i=max(parentid) from #os update #os set fullpath=id while @j<=@i begin update #os set fullpath=a.fullpath+','+ltrim(#os.id) from #os inner join #os a on #os.parentid=a.id where #os.parentid=@j set @j=@j+1 end select * from #os go --调用存储过程 exec wsp2 1
那我也传一下我自己写的。 drop table #tt create table #tt ( a varchar(50), b int )declare @s varchar(50) set @s='C1'----------需要求的drop table #t create table #t ( a varchar(50), b int, c varchar(50), d int, e int )declare @i int set @i=1insert into #t select a.productid,a.amount,a.parentproduct,a.amountx,0 from bom_BillListTab a where a.parentproduct=@swhile @i<5 begin insert into #t select e.*,@i from (select a.productid,a.amount*(select sum(b) from #t t1 where t1.a=a.parentproduct) as amount,a.parentproduct,a.amountx*(select sum(b) from #t t1 where t1.a=a.parentproduct) as amountx from bom_BillListTab a where a.parentproduct in( select #t.a from #t where #t.e=@i-1))e if @@rowcount=0 break set @i=@i+1 end insert into #tt SELECT A,SUM(B) AS NUM FROM #T WHERE E=1 GROUP BY Aselect a,sum(b) as [all] from #tt group by a
----bom结构,查找节点下所有子节点:create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'--求个节点下所有子节点:
create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,parentid int,desn varchar(10),lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from os where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids
end
go--调用函数
select *,ids=dbo.f_cid(id) from os
--得到每个节点路径:
create proc wsp2
@id int
as
select *,cast(' ' as varchar(10)) fullpath into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id
while @j<=@i
begin
update #os set fullpath=a.fullpath+','+ltrim(#os.id)
from #os inner join #os a on #os.parentid=a.id
where #os.parentid=@j
set @j=@j+1
end
select * from #os
go
--调用存储过程
exec wsp2 1
drop table #tt
create table #tt
(
a varchar(50),
b int
)declare @s varchar(50)
set @s='C1'----------需要求的drop table #t
create table #t
(
a varchar(50),
b int,
c varchar(50),
d int,
e int
)declare @i int
set @i=1insert into #t
select a.productid,a.amount,a.parentproduct,a.amountx,0 from bom_BillListTab a
where
a.parentproduct=@swhile @i<5
begin
insert into #t
select e.*,@i from
(select a.productid,a.amount*(select sum(b) from #t t1 where t1.a=a.parentproduct) as amount,a.parentproduct,a.amountx*(select sum(b) from #t t1 where t1.a=a.parentproduct) as amountx from bom_BillListTab a
where
a.parentproduct in( select #t.a from #t where #t.e=@i-1))e
if @@rowcount=0
break
set @i=@i+1
end
insert into #tt
SELECT A,SUM(B) AS NUM FROM #T
WHERE E=1
GROUP BY Aselect a,sum(b) as [all] from #tt
group by a