如何在sql Server 2008中递归查询... [英] How do I Recursive Query in sql Server 2008...

问题描述

需要计算indebtedness列的值，以便在opensBalance！= 0时，然后indebtedness = openingBalnce + SalesTotal-SalesReturn。但是，当opensBalnce = 0时，indebtedness =上一个月的销售额具有相同SalesID的销售额。如果前一个值= 0，则获取前一个值并继续获取上一个值，直到此列中有值：

need to calculate the value of indebtedness column so that when openingBalance !=0 then indebtedness = openingBalnce+SalesTotal-SalesReturn. But, when openingBalnce = 0 then indebtedness = indebtedness of the previous monthSales with the same SalesID. If the previous value = 0 get the previous value and continue get previous value till have value in this column:

SalesMonth SalesID  openingBalance  SalesTotal SalesReturn  Indebtednes
1         |  1    |     352200   |    0     |    5600     |   Null
1         |  2    |     50000    |   1100   |      0      |   Null
1         |  3    |     9500     |   6000   |      0      |   Null
2         |  1    |     0        |    0     |     1200    |   Null
2         |  2    |     0        |   300    |      0      |   Null
2         |  3    |     0        |   500    |     1000    |   Null
3         |  1    |     0        |    600   |      0      |   NULL
3         |  2    |     0        |   200    |      0      |   NULL
3         |  3    |    0         |     0    |       10    |   NULL
.
.
.
12           1             0          0           0              NULL
12           2             0          0           0              NULL
12           3             0          0           0              NULL

和这样的输出：

and the output like this:

when openingBalance !=0 then Indebtednes=openingBalnce+SalesTotal-SalesReturn
when openingBalnce =0 then Indebtednes=Indebtednes (of the previous
month of the same SalesID)+SalesTotal-SalesReturn.

SalesMonth SalesID  openingBalance  SalesTotal SalesReturn  Indebtednes
  1         |  1    |     352200   |    0     |    5600     |   346600
  1         |  2    |     50000    |   1100   |      0      |   51100
  1         |  3    |     9500     |   6000   |      0      |   15500
  2         |  1    |     0        |    0     |     1200    |   345400
  2         |  2    |     0        |   300    |      0      |   51400
  2         |  3    |     0        |   500    |     1000    |   15000
  3         |  1    |     0        |   600    |      0      |   346000
  3         |  2    |     0        |   200    |      0      |   51600
  3         |  3    |     0        |     0    |     10      |   14990
  .
  .
  .
  12           1             0          0           0              NULL
  12           2             0          0           0              NULL
  12           3             0          0           0              NULL

推荐答案

你正在寻找运行金额。



看看过去的答案 [ ^ ]。

You're looking for "running sum".

Have a look at past answers[^].

这篇关于如何在sql Server 2008中递归查询...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！