parentID为父节点 child为子节点 ，Num为子节点的数量，子节点也可能为父节点、
编写一个存储过程，输出一个父节点的所有最终节点和数量，
parentID child Num
a b 11
a c 3
b d 4
b e 5
比如a节点有11个b节点和3个c节点组成，而b节点有4个d节点和5个e节点构成，写一个存储过程，输入一个a节点，找出所有a的最终叶子节点的名字和数量。
---------------------------------------------------------------
create proc proc_queryNum
@parentID varchar(10)
as
declare @tmp table (parentID varchar(10), child varchar(10), Num int)
insert @tmp
select * from tablename where parentID=@parentID
while exists (
select * from tablename a,@tmp b
where a.parentID=b.child
and not exists (
select * from @tmp
where parentID=a.parentID
and child=a.child
)
)
insert @tmp
select a.parentID,a.child,a.Num*b.Num from tablename a,@tmp b
where a.parentID=b.child
and not exists (
select * from @tmp
where parentID=a.parentID
and child=a.child
)
select child,Num from @tmp
where child not in (select distinct parentID from @tmp)
go
没有测试！
2k一下版本无效！
---------------------------------------------------------------
declare @count int
declare @id char
set @id = 'a'
set @count = 0
create table #temp1(
child char,
num int,
flag char
)
insert into #temp1 select child, num, '0' from a04 where parentid = @id
select @count = count(child) from #temp1 where flag = '0'
while @count > 0
begin
update #temp1 set flag = '1' where flag = '0'
insert into #temp1 select a04.child, a04.num, '0' from a04, #temp1 where a04.parentid = #temp1.child and #temp1.flag = '1'
update #temp1 set flag = '2' where flag = '1'
select @count = count(child) from #temp1 where flag = '0'
end
select child, num from #temp1
drop table #temp1