依据BOM表计算材料，求MSSQL 2005代码

根据BOM表计算材料，求MSSQL 2005代码
BOM表
产品名称    物料名称   用量
产品1          物料A         1
产品1          物料B         2
产品1          物料C         3

物料B          物料B1     2
物料B          物料B2     2

物料C          物料B1      1
物料C          物料C1      2


生产计划表
产品名称    数量
产品1          10

想要的结果
产品名称   物料名称   需求数量
产品1        物料A         10
产品1        物料B1       70
产品1        物料B2       40
产品1        物料C1       60

新手，分数不多，求高手帮忙
------解决思路----------------------

use tempdb
if object_id('bom') is not null
drop table bom
go
create table Bom(productName Nvarchar(100),materialName Nvarchar(100),usedqty int)
insert into bom(productName,materialName,usedqty)

select '产品1',          '物料A' ,        1  union all
select '产品1',          '物料B' ,        2 union all
select '产品1',          '物料C' ,        3 union all
select '物料B',          '物料B1',     2 union all
select '物料B',          '物料B2',     2 union all
select '物料C',          '物料B1',      1 union all
select '物料C',          '物料C1',      2

;with sel as
(select productname,materialname,usedqty,1 as lev from bom
where productname='产品1'
union all
select a.productname,a.materialname,a.usedqty*b.usedqty,b.lev+1
from bom a 
join sel b on a.productname=b.materialname
) 
select '产品1' as productname,materialname,sum(usedqty)*10 as qty from sel 
where materialname not in(select productname from sel )
group  by materialname

------解决思路----------------------

引用:

是要用递归，层数不确定的

with cte as
( select * from Bom as a where not exists 
(select * from Bom as b where b.productName=a.materialName)
union all 
select a.productname,coalesce(b.materialname,a.materialname) as materialname,
ISNULL(b.usedqty,1)*a.usedqty as usedqty
from Bom as a  join cte as b
on a.materialname=b.productname)

select a.productName,a.materialName,a.sums*qty as sums from (select productName,materialName,SUM(usedqty) as sums from cte
where productName like'产品%'
group by productName,materialName)as a join 
plans as b on a.productName=b.name

------解决思路----------------------

引用:

WITH CTEBOM AS(
SELECT A.*,B.productName ParentProductName,(SELECT TOP 1 1 FROM BOM C WHERE C.productName=A.materialName)ExistsMaterialName
FROM
Bom A LEFT JOIN BOM B ON A.productName=B.materialName
)
,CTE AS(
SELECT productName,materialName,usedqty,ExistsMaterialName,productName TopProductName FROM CTEBOM WHERE ParentProductName IS NULL
UNION ALL
SELECT A.productName,A.materialName,A.usedqty*B.usedqty,A.ExistsMaterialName,B.TopProductName FROM CTEBOM A INNER JOIN CTE B ON B.materialName=A.productName
)
SELECT
A.[name],B.materialName,SUM(A.[qty]*B.usedqty)usedqty
FROM [plans] A LEFT JOIN CTE B ON A.[name]=B.TopProductName
WHERE B.ExistsMaterialName IS NULL
GROUP BY
A.[name],B.materialName order by name

按你9楼的，就是产品名称改为name,数量改为qty,生产计划表改为plans

你没错，是我的错
已修改如下

;WITH CTEBOM AS(