有这样的一个表:
create table bom
(
Pitemid varchar(20),
Itemid varchar(20),
Qty real
)
还有这样一个结构图:
A

B(2) C(3)

D(3) E(1) F(5) G(1)

H(2) L(10)

I(5)
通过分析得出这样的数据:
Pitemid itemid qty
A B 2
A C 3
B D 3
B E 1
D H 2
D L 10
H I 5
C F 5
C G 1
要用SQL 语句查询出这样的结果：
Itemid Qty
B 2
C 3
D 6
E 2
H 12
I 60
L 30
F 15
G 3
请哪位高手帮我解决一下～～～～

--Check the Qty of Item I

create table bom
(
Pitemid varchar(20),
Itemid varchar(20),
Qty real
)

insert into bom
select 'A','B',2 union all
select 'A','C',3 union all
select 'B','D',3 union all
select 'B','E',1 union all
select 'D','H',2 union all
select 'D','L',10 union all
select 'H','I',5 union all
select 'C','F',5 union all
select 'C','G',1
go
create function fn_test(@id varchar(20))
returns int
as
begin
declare @return int
select @id=Pitemid,@return=isnull(@return,1)*Qty from bom where itemid=@id
while @@rowcount!=0
select @id=Pitemid,@return=isnull(@return,1)*Qty from bom where itemid=@id
return (@return)
end
go
select Itemid,dbo.fn_test(Itemid) as Qty
from bom
order by itemid
go

drop table bom
drop function fn_test

Itemid Qty
-------------------- -----------
B 2
C 3
D 6
E 2
F 15
G 3
H 12
I 60
L 60

哦,我明白拉,谢谢高人指导啊~~