如何使这个查询递归Sql Server？

I have this table structure for Balances table:

我有Balances表的这个表结构:

And this is the view:

这就是观点:

I have also this structure for Amounts table:

我还有Amounts表的这个结构:

This is the view mode for Amounts table:

这是Amounts表的视图模式:

First of all I need to get the amount value for a specific day in Amounts Table:

首先,我需要在Amounts表中获取特定日期的金额值:

with this query I get the amount 300 in date 07/07/2016. Once achieved this figure, I need to make a recursive query with Balances table. The end result should be like this:

有了这个查询,我在2016年7月7日获得金额300。一旦达到这个数字,我需要使用Balances表进行递归查询。最终结果应该是这样的:

    Name   abstractAmount   addAmount  Balance
   -----   --------------   ---------  -------
   Josep                      100        400       
   Maria       50                        350
   George                     60         410
   Julianne    25                        385

what is this? This result is achieved taking the 300 from the Amounts table, and for each row in Balance table I see: If the abstracAmount in the first row is not empty, I make this mathematical calculation: balance = (300 - abstractAmount), in case is empty and the addAmount column has values I make this mathematical calculation balance = (300 + addAmount) In the rest of rows I do the same but the calculation is not on 300, is on the last row balance: For example: In the first row the balance is 400 because the addamount has value so I make this calculation : 300 + 100 = 400 In the second row the balance is 350 because the abstractAmount is not empty so I take the balance value for the last row and make this calculation : 400 - 50 = 350. And the same thing for the rest of rows, only the first row takes the balance value for the amounts table.

这是什么?这个结果是从Amounts表中获取300,并且对于Balance表中的每一行我看到:如果第一行中的abstracAmount不为空,我进行这个数学计算:balance =(300 - abstractAmount),万一是empty和addAmount列有值我做这个数学计算balance =(300 + addAmount)在其余的行中我做同样但计算不在300上,在最后一行上是平衡:例如:在第一行余额为400因为addamount有值所以我进行此计算:300 + 100 = 400在第二行中,余额为350,因为abstractAmount不为空所以我取最后一行的余额值并进行此计算:400 - 50 = 350.对于其余行,同样的事情,只有第一行采用金额表的余额值。

Notes:
1. Always the column abstractAmount subtracts values, and the addAmount column sum values.

注意:1。始终列abstractAmount减去值和addAmount列的总和值。

Always one of this columns (abstractAmount | addAmount) will be empty .

始终其中一列(abstractAmount | addAmount)将为空。

Only the first row takes the value to make the mathematical calculation for the Amounts table, the rest of rows takes the value for the row before.

只有第一行获取值才能对Amounts表进行数学计算,其余行将获取之前行的值。

How can I get this final result? :

我怎样才能得到这个最终结果? :

       Name     abstractAmount  addAmount   Balance
       -----   --------------   ---------  -------
       Josep                      100        400       
       Maria       50                        350
       George                     60         410
       Julianne    25                        385

I accept suggestions, thanks.

我接受建议,谢谢。

1 个解决方案

#1

Instead of recursion, you can use window functions. More specifically a sum over rows unbounded preceding to get a running total (+ the start balance):

您可以使用窗口函数代替递归。更具体地说,在获得运行总计(+起始余额)之前*限的行数之和:

select *,300 +  sum(isnull(addAmount,0) - ISNULL(abstractAmount,0))  over (order by id rows unbounded preceding) Balance 
from Balances

The isnull(addAmount,0) - ISNULL(abstractAmount,0) is simply the mutation for every row. The over (order by id rows unbounded preceding) scopes the sum to the current row and all preceding rows according to id.

isnull(addAmount,0) - ISNULL(abstractAmount,0)只是每一行的变异。 over(按前面*限的id行的顺序)根据id将和的范围限定为当前行和所有前面的行。

To get the base from the amounts table, you can have simply have the (select ...where date..) as a value instead of '300' or a bit more nifty: with a cross join to the amounts table:

要从金额表中获取基数,您可以简单地将(选择... where date ..)作为值而不是“300”或更加漂亮:使用金额表的交叉连接:

select b.*, a.dateInsertion,a.amount, a.amount +  sum(isnull(addAmount,0) - ISNULL(abstractAmount,0))  over (order by b.id rows unbounded preceding) Balance 
from Balances b
cross join Amounts a
where a.dateInsertion = '20160707'

With the cross join without the where, you would get all possible balances

通过没有where的交叉连接,您将获得所有可能的余额

#1