BOM问题

[streetclm]

假如有个单号A，展开如下
level      item        child       QTY
1          A           B           10
2          B           C           10
2          B           D           90
2          B           E           20
3          C           F           10
3          C           G           20

就是说QTY只对应他的前一级。以第2条记录来说，B零件需要10个C零件，这没问题，
但是因为我是用A零件展开的，所以我希望，所有的QTY都是对应A零件的，所以第2条记录的
QTY应该是 10×10=100

希望的结果如下：
level      item        child       QTY
1          A           B           10
2          B           C           100
2          B           D           900
2          B           E           200
3          C           F           1000
3          C           G           2000

请问大侠应该怎么写

[SQLCenter]

这样效率高点

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(level int, item varchar(8), child varchar(8), QTY int)
insert into #
select 1, 'A', 'B', 10 union all
select 2, 'B', 'C', 10 union all
select 2, 'B', 'D', 90 union all
select 2, 'B', 'E', 20 union all
select 3, 'C', 'F', 10 union all
select 3, 'C', 'G', 20

;with cte as
(
	select topitem=item, * from # t where not exists (select 1 from # where child=t.item)
	union all
	select b.topitem, a.level, a.item, a.child, a.Qty*b.Qty from # a join cte b on a.item=b.child
)
select * from cte t

/*
topitem  level       item     child    QTY
-------- ----------- -------- -------- -----------
A        1           A        B        10
A        2           B        C        100
A        2           B        D        900
A        2           B        E        200
A        3           C        F        1000
A        3           C        G        2000
*/

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(level int, item varchar(8), child varchar(8), QTY int)
insert into #
select 1, 'A', 'B', 10 union all
select 2, 'B', 'C', 10 union all
select 2, 'B', 'D', 90 union all
select 2, 'B', 'E', 20 union all
select 3, 'C', 'F', 10 union all
select 3, 'C', 'G', 20

;with cte as
(
	select topitem=item, * from #
	union all
	select b.topitem, a.level, a.item, a.child, a.Qty*b.Qty from # a join cte b on a.item=b.child
)
select * from cte where topitem='A'

/*
topitem  level       item     child    QTY
-------- ----------- -------- -------- -----------
A        1           A        B        10
A        2           B        C        100
A        2           B        D        900
A        2           B        E        200
A        3           C        F        1000
A        3           C        G        2000
*/

[streetclm]

感谢
[Quote=引用 2 楼 sqlcenter 的回复:]

这样效率高点

SQL code
--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(level int, item varchar(8), child varchar(8), QTY int)
insert into #
select 1, 'A', 'B', 10 union a……
[/Quote]