IF OBJECT_ID('Tempdb..#TEMP','U') IS NOT NULL DROP TABLE #TEMP

;WITH BOM(NumAtCard,Father,ItemCode,Quantity,iLevel)

AS ( SELECT u0.U_OrderNo ,T0.Code,T1.Code ,CONVERT(NUMERIC(23,15), (T1.Quantity/T0.Qauntity)U0.Quantity) Quantity , 1 iLevel

FROM RDR1 U0

JOIN OITT T0 ON U0.ItemCode =T0.Code

JOIN ITT1 T1 ON T0.Code = T1.Father

UNION ALL

SELECT T0.NumAtCard,T0.Father,T2.Code,CONVERT(NUMERIC(23,15),(T2.Quantity/T1.Qauntity)T0.Quantity) ,T0.iLevel+1FROM BOM T0 JOIN OITT T1 ON T0.ItemCode = T1.Code JOIN ITT1 T2 ON T1.Code = T2.Father )

SELECT R0.ItemCode,SUM(R0.Quantity )NeedQty

INTO #TEMP FROM BOM R0WHERE ItemCode ='2DH2002-01-081-000'GROUP BY R0.ItemCode

SELECT U0.ItemCode,MAX(U1.NeedQty) 订单需求,SUM(U0.InQty) 进仓,SUM(U0.OutQty) 出仓

FROM OIVL U0 JOIN #TEMP U1 ON U0.ItemCode =U1.ItemCode GROUP BY U0.ItemCode