对数据集执行“负传播”

通常对于诸如运行总计之类的操作，使用游标实际上比其他一些方法更有效。不要害怕使用游标，除非您看到明显的性能问题。

在 SQL Server 2012 中，有新的窗口函数使它变得更好，但显然你不能使用它们。有一种古怪的更新方法“有效”，但该语法不受官方支持，并且在 SQL Server 2012 之后可能无法正常工作——它可能有一天会成为非法语法，并且永远无法保证排序的工作方式。典型的基于集合的方法不能很好地扩展——使用游标通常会更好，因为您只需扫描每一行一次，而使用基于集合的解决方案，扫描会非线性增长。我希望我能向您展示我在这方面所做的工作，但揭示这一切的博客文章的发布日期仍然未知。

至少在声明游标时使用：

DECLARE ... CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

这也可能是有用的阅读：

• 要改掉的坏习惯：认为 while 循环不是游标

多亏了 Aaron 的建议，我没有尝试让整个事物都基于集合，而是尝试在集合最适用的地方做集合，然后对其余部分使用游标。

我还能够改变必须只从最旧的信用中提取的要求，现在我可以将每个客户的信用汇总到一个桶中并与之对应。

现在快多了，这是更新版本

--indexes to speed up first two queires
IF not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_AR_BALANCES]') AND name = N'IX_IDATARBALANCES_CLIENTID_GUID')
    CREATE NONCLUSTERED INDEX IX_IDATARBALANCES_CLIENTID_GUID
    ON [dbo].[IDAT_AR_BALANCES] ([CLIENT_ID])
    INCLUDE ([cvtGUID])

IF not EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_AR_BALANCES]') AND name = N'IX_IDATARBALANCES_AMOUNT_ID_DATE')   
    CREATE NONCLUSTERED INDEX [IX_IDATARBALANCES_AMOUNT_ID_DATE]
    ON [dbo].[IDAT_AR_BALANCES] ([AMOUNT])
    INCLUDE ([CLIENT_ID],[AGING_DATE])

--Remove AR that totals to 0.
DELETE FROM IDAT_AR_BALANCES 
WHERE client_id IN ( 
SELECT client_id 
FROM IDAT_AR_BALANCES 
GROUP BY client_id 
HAVING SUM(amount) = 0)

--find all instances that credit > balance
SELECT newid() as cvtGUID, client_id, max(AGING_DATE) as AGING_DATE, sum(AMOUNT) as amount
into #creditLarger
FROM IDAT_AR_BALANCES 
GROUP BY client_id 
HAVING SUM(amount) < 0

--remove all of the creditLargerEntries
delete IDAT_AR_BALANCES where client_id in (select client_id from #creditLarger)

--Build a list of remaining balances and summed credits
select * into #balances from [IDAT_AR_BALANCES] where amount > 0

SELECT newid() as cvtGUID, client_id, max(AGING_DATE) as AGING_DATE, sum(AMOUNT) as amount
into #credits 
FROM [IDAT_AR_BALANCES] 
where amount < 0 
GROUP BY client_id

--Index to make the update faster
CREATE NONCLUSTERED INDEX BALANCE_INDEX ON #balances ([CLIENT_ID],[AMOUNT])

--Begin loop of processing credits
set nocount on
declare credit_cursor cursor LOCAL STATIC READ_ONLY FORWARD_ONLY for select top 10 [CLIENT_ID], amount, cvtGUID from #credits 
open credit_cursor 

declare @client_id varchar(11) 
declare @credit money 
declare @balance money 
declare @cvtGuidBalance uniqueidentifier 
declare @cvtGuidCredit uniqueidentifier 

fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit 
while @@fetch_status = 0 
begin 
  --While balances exist for the current client_ID and there are still credits to be applied, loop.
  while(@credit < 0 and exists(select * from #balances where @client_id = CLIENT_ID and amount > 0)) 
  begin 
    --Find the oldest oustanding balance.
    select top 1  @balance = amount, @cvtGuidBalance = cvtGuid 
    from #balances 
    where @client_id = CLIENT_ID and amount <> 0 
    order by AGING_DATE 

    -- merge the balance and the credit
    set @credit = @balance + @credit 

    if(@credit > 0) 
    begin 
      --If the credit is now postive save the leftover in the currently selected balance and set the credit to 0
      update #balances set amount = @credit where cvtGuid = @cvtGuidBalance  
      set @credit = 0 
    end
    else
      -- Credit is larger than the balance, 0 out the balance and continue processesing
      update #balances set amount = 0 where cvtGuid = @cvtGuidBalance  

  end -- end of while loop

  --There are no more balances to apply the credit to, save it back to the list.
  update #credits set amount = @credit where cvtGuid = @cvtGuidCredit 

  --Get the next credit.
  fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit 
end 
close credit_cursor 
deallocate credit_cursor
set nocount off

truncate table [IDAT_AR_BALANCES]

insert into [IDAT_AR_BALANCES] 
    select * from #balances
    union select * from #credits
    union select * from #creditLarger

--Delete any balances and credits that where 0'ed out durning the spred negitive.
delete [IDAT_AR_BALANCES] where amount = 0

drop table #balances
drop table #credits
drop table #creditLarger