新增文章

文章标题分类内容

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 【分组 FCustName】【排序 FDate,FBillNo,FIndex】

select rank() over ( partition by FCustName order by FDate,FBillNo,FIndex) as FSort ,* from a_CustBill ;

with table1 as(
      SELECT FCustName,FSort,sum(FAmountSale)-sum(FAmountRec) FAmountCal
      FROM(
         select rank() over ( partition by FCustName order by FDate,FBillNo,FIndex) as FSort ,* from a_CustBill
      ) A 
      GROUP BY FCustName,FSort
   )
   select 
       a1.FSort,a1.FCustName,a1.FClassTypeName,a1.FDate,a1.FBillNo,a1.FIndex,a1.FItemName,a1.FQtySale,a1.FUnitSale,a1.FPriceSale
       
      ,a1.FAmountSale
      ,a1.FAmountRec
      ,sum(a2.FAmountCal) FAmountCal 
      
      ,a1.FBillNo_Order,a1.FNoteItem
      
   from ( select rank() over ( partition by FCustName order by FDate,FBillNo,FIndex) as FSort ,* from a_CustBill  ) a1 ,table1 a2 
   
   where a1.FCustName = a2.FCustName and a1.FSort >= a2.FSort
   
   group by a1.FCustName,a1.FSort,a1.FClassTypeName,a1.FDate,a1.FBillNo,a1.FIndex,a1.FItemName,a1.FQtySale,a1.FUnitSale,a1.FPriceSale,a1.FAmountSale,a1.FAmountRec,a1.FBillNo_Order,a1.FNoteItem ;