SQL递归查询(with cte as) 物料分解

需求

最近在做一个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) 

效果如下，结果正确：