关与一个查询　问题　各位帮忙看看　谢谢
table
顶层物料　父项　 子料　 制／购　类型　　用量
10001 10001 10001-1 M P 1
10001 10001 10001-2 M P 2
10001 10001-2 10001-25 M P 2
10001 10001-1 A M N 2
10001 10001-25 B M N 2

我想找子项中类型为N, 制／购为M的资料的用量（最顶层为１）
10001 10001-1 A M N ２
10001 10001-25 B M N ８




[解决办法]
我想找子项中类型为N, 制／购为M的资料的用量（最顶层为１）
10001 10001-1 A M N ２--这里是不是应该3 ？
10001 10001-25 B M N ８


[解决办法]
SELECT *,SUM(用量) 总用量 FROM table WHERE 类型= 'N ' AND [制／购]= 'M '
[解决办法]
SELECT * FROM table WHERE 类型= 'N ' AND [制／购]= 'M '
[解决办法]
我想找子项中类型为N, 制／购为M的资料的用量（最顶层为１）
=========================================================
没明白，什么子项？能不能说的明白点？
[解决办法]
SELECT * FROM table WHERE 类型= 'N ' AND [制／购]= 'M '

[解决办法]
--了跑游的,得@_@ ,bom表很的,有routing的影


--建立函,查找所有父
Create function dbo.fn_test_bom( @child_part varchar(30))
returns @t_level table(part varchar(30),level int)
AS
begin
declare @level int
set @level=0
insert into @t_level select @child_part,@level
while @@rowcount> 0
begin
set @level=@level+1
insert into @t_level select a.parent_part,@level
from [Test_bom] a,@t_level b
where a.child_part=b.part
and b.level=@level-1
end
return
end

Go
create table Test_bom( topic varchar(10), parent_part varchar(20), child_part varchar(20), [制/] varchar(10),型 varchar(10),quantity int )
insert into Test_bom
select '10001 ', '10001 ', '10001-1 ', 'M ', 'P ',1 union all
select '10001 ', '10001 ', '10001-2 ', 'M ', 'P ',2 union all
select '10001 ', '10001-2 ', '10001-25 ', 'M ', 'P ',2 union all
select '10001 ', '10001-1 ', 'A ', 'M ', 'N ',2 union all

select '10001 ', '10001-25 ', 'B ', 'M ', 'N ',2

Go
create table #t( topic varchar(10), parent_part varchar(20), child_part varchar(20), [制/] varchar(10),型 varchar(10),quantity int )

declare @child varchar(20)
declare c1 cursor for
select child_part
from Test_bom where [制/]= 'M ' and 型= 'N '
open c1
fetch next from c1 into @child
while @@fetch_status=0
begin
declare @qty int,@tmp int
set @qty=1
declare c2 cursor for
select quantity from dbo.fn_test_bom(@child) a,Test_bom b
where a.part=b.child_part
open c2
fetch next from c2 into @tmp
while @@fetch_status=0
begin
select @qty=@qty*@tmp
fetch next from c2 into @tmp
end
insert into #t
select topic,parent_part,child_part, [制/] ,型,quantity=@qty
from Test_bom
where [制/]= 'M ' and 型= 'N '
and child_part=@child
close c2
deallocate c2
fetch next from c1 into @child
end
close c1
deallocate c1
Go

select * from #t

drop table #t,Test_bom
drop function fn_test_bom