SQL recursive query (with CTE as) material decomposition

Demand

Recently, in an MRP project, the raw materials and quantities needed for the finished product are broken down from the original boring table according to the plan of production release.

Reference

Http://www.cnblogs.com/xqhppt/archive/2011/02/15/1955366.html

Http://www.cnblogs.com/guoysh1987/archive/2011/12/23/2299379.html

Code implementation

SQL data table structure

CREATE TABLE [dbo].[cproduction](    [ID] [int] IDENTITY(1,1) not NULL,    [Production] [varchar](Max)NULL,    [Materialno] [varchar]( -)NULL,    [Compquan] [float] NULL)  on [PRIMARY]GO

View Code

INSERT  intoCproductionVALUES('JCV3311149605','a5e01194561',1)INSERT  intoCproductionVALUES('JCV3311149605','JCV2511141838',1)INSERT  intoCproductionVALUES('JCV3311149605','JCV2511141929',1)INSERT  intoCproductionVALUES('JCV2511141838','JCV3311800707',1)INSERT  intoCproductionVALUES('JCV3311800707','JCVRM00040003',1)INSERT  intoCproductionVALUES('JCV3311800707','JCVRM00040004',1)

View Code

SQL code Implementation

Assuming that the plan for product JCV3311149605 is 1000

 withCte as(Select [Production],[Materialno],[Compquan]* +  asQuan1  asLvl,path=CAST(Compquan as INT) fromCproductionwhereProduction= 'JCV3311149605' Union  All SelectD.[Production]D.[Materialno], C.path*D.[Compquan]* +  asQuan,lvl+1, Path=CAST(D.compquan*C.path as int) fromCTE CInner JoinCproduction D onC.materialno=d.production)Select *  fromCteOPTION(maxrecursion0)

View Code

The results are correct:

SQL recursive query (with CTE as) material decomposition