新增文章

文章标题分类内容

递归累计 逐行累计 余额
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 【分组 FCustName】【递归计算依据 a1.FMaterialName = a2.FMaterialName  and a1.FLot_Text = a2.FLot_Text and a1.FSort >= a2.FSort 】 FQtyPass,FQtyUnPass

with table1 as(
      SELECT FMaterialName,FLot_Text ,FSort ,sum(FQtyPass) FQtyPassSum ,sum(FQtyUnPass) FQtyUnPassSum
      FROM(
         select rank() over ( partition by FMaterialName,FLot_Text order by FDate,FMachineName,FShiftGroupName) as FSort ,* from aTmp
      ) A 
      GROUP BY FMaterialName,FLot_Text ,FSort
   )
   select 
       a1.FSort,a1.FMaterialName,a1.FLot_Text,a1.FDate,a1.FMachineName,a1.FShiftGroupName
       
      ,a1.FQtyInput,a1.FQtyProduce,a1.FQtyPass,a1.FQtyUnPass

,sum(a2.FQtyPassSum  ) FQtyPassSum 
      ,sum(a2.FQtyUnPassSum) FQtyUnPassSum 
      
   from ( select rank() over ( partition by FMaterialName,FLot_Text order by FDate,FMachineName,FShiftGroupName) as FSort ,* from aTmp  ) a1 ,table1 a2 
   
   where a1.FMaterialName = a2.FMaterialName 
     and a1.FLot_Text     = a2.FLot_Text

and a1.FSort >= a2.FSort
   
   group by a1.FSort,a1.FMaterialName,a1.FLot_Text,a1.FDate,a1.FMachineName,a1.FShiftGroupName 
           ,a1.FQtyInput,a1.FQtyProduce,a1.FQtyPass,a1.FQtyUnPass

order by a1.FMaterialName,a1.FLot_Text,a1.FDate,a1.FMachineName,a1.FShiftGroupName