新增文章

文章标题分类内容

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 重排序号：

update a1 set FSort = a2.FSort
   
   from aTmp_BankList a1
   join (
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      select  FIdentityId ,rank() over ( partition by FBankNo,FCurrencyName order by FLevel,FDate ,(case when FAmountRec > 0 then 1 when FAmountPay > 0 then 2 else 3 end) ,FBillNo,FSeq ) FSort

from aTmp_BankList  where FUserId = @FUserId
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   ) a2 on a1.FIdentityId = a2.FIdentityId

where FUserId = @FUserId ;

with table1 as(
      select FBankNo,FCurrencyName ,FSort ,sum(FAmountCal) FAmountCal
      from aTmp_BankList 
      where FUserId = @FUserId 
      group by FBankNo,FCurrencyName ,FSort
   )
   update a1 set FAmountEnd = a2.FAmountCal
   
   from aTmp_BankList a1
   join (
-----------------------------------------------------------------------------------------------------------
   select 
       a1.FSort,a1.FBankNo,a1.FCurrencyName  ,sum(a2.FAmountCal) FAmountCal
      
   from aTmp_BankList a1 ,table1 a2 
   
   where FUserId = @FUserId

and a1.FBankNo       = a2.FBankNo 
     and a1.FCurrencyName = a2.FCurrencyName

and a1.FSort >= a2.FSort
   
   group by a1.FSort,a1.FBankNo,a1.FCurrencyName
-----------------------------------------------------------------------------------------------------------
   ) a2 on a1.FBankNo = a2.FBankNo and a1.FCurrencyName = a2.FCurrencyName and a1.FSort = a2.FSort
   ;