需求
最近在做一个MRP的项目，需要根据生产下达的计划从原始无聊表中分解出成品所需要的原材料和数量.
参考
http://www.cnblogs.com/xqhppt/archive/2011/02/15/1955366.html
http://www.cnblogs.com/guoysh1987/archive/2011/12/23/2299379.html
代码实现
SQL数据表结构
CREATE TABLE [dbo].[cProduction](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Production] [varchar](max) NULL,
[MaterialNo] [varchar](100) NULL,
[CompQuan] [float] NULL
) ON [PRIMARY] GO
INSERT INTO cProduction VALUES('JCV3311149605','A5E01194561',1)
INSERT INTO cProduction VALUES('JCV3311149605','JCV2511141838',1)
INSERT INTO cProduction VALUES('JCV3311149605','JCV2511141929',1)
INSERT INTO cProduction VALUES('JCV2511141838','JCV3311800707',1)
INSERT INTO cProduction VALUES('JCV3311800707','JCVRM00040003',1)
INSERT INTO cProduction VALUES('JCV3311800707','JCVRM00040004',1)
SQL代码实现
假设对产品JCV3311149605下达的计划为1000
with cte as(
select [Production],[MaterialNo],[CompQuan]*1000 as quan,1 as lvl,path=CAST(CompQuan AS INT) from cProduction where Production = 'JCV3311149605'
union all
select d.[Production],d.[MaterialNo],C.path*d.[CompQuan]*1000 as quan,lvl+1,path=CAST(D.CompQuan*c.path AS int) from cte c inner join cProduction d on c.materialno = d.Production
) select * from cte OPTION(MAXRECURSION 0)
效果如下，结果正确：