使用sql计算余额

Question

我正在使用以下查询来获取某个客户的余额

SELECT  t.[InvoiceID], t.S_Type as Type,
        t.Date, t.Debit, t.Credit, b.Balance
FROM Statement as t CROSS apply
     (SELECT Balance = SUM(Debit) - SUM(Credit)
      FROM Statement as x
      WHERE (x.date < t.date or
             x.date = t.date 
            ) AND
            x.CustID = t.CustID
     ) b
WHERE t.CustID ='1' and date between '2015-01-01' and '2016-01-12'
order by InvoiceID, Type desc, Date

输出

InvoiceID   Type            Date       Debit    Credit  Balance
3         Sales Invoice     2015-06-09  200.00  0.00    225.00
3         Receipt Voucher   2016-01-04  0.00    200.00  0.00
5         Sales Invoice     2015-06-09  25.00   0.00    225.00
5         Receipt Voucher   2016-01-04  0.00    25.00   0.00

正如你所看到的，平衡值在正确的中

应该是

InvoiceID   Type                  Date     Debit    Credit  Balance
    3         Sales Invoice     2015-06-09  200.00  0.00    200.00
    3         Receipt Voucher   2016-01-04  0.00    200.00  0.00
    5         Sales Invoice     2015-06-09  25.00   0.00    25.00
    5         Receipt Voucher   2016-01-04  0.00    25.00   0.00

更新

当我将x.CustID=t.CustID更改为x.InvoiceID=t.InvoiceID时，我尝试与另一个拥有所有销售发票的客户合作，余额给出相同的借方值，而不是借方值的总和

输出

InvoiceID   Type               Date     Debit   Credit  Balance
1          Sales Invoice    2015-06-09  200.00  0.00    200.00
10         Sales Invoice    2015-06-09  850.00  0.00    850.00
12         Sales Invoice    2015-06-09  20.00   0.00    20.00
59         Sales Invoice    2015-09-03  0.00    0.00    0.00

Answer 1

您可以使用以下语句来获得正确的结果：

SELECT  t.[InvoiceID], t.S_Type as Type,
        t.Date, t.Debit, t.Credit, b.Balance
FROM Statement as t CROSS apply
     (SELECT Balance = SUM(Debit) - SUM(Credit)
      FROM Statement as x
      WHERE (x.date < t.date or
             x.date = t.date 
            ) AND
            x.InvoiceID = t.InvoiceID 
            AND x.CustID = t.CustID
     ) b
WHERE t.CustID ='1' and date between '2015-01-01' and '2016-01-12'
order by InvoiceID, Type desc, Date

Answer 2

结果在我看来是正确的。结果的第一行返回invoiceId = 3，余额列包括invoiceId=5的值，该值在同一天发生在同一客户端，因此应该包括在内。如果您只需要发票中的值，则将x.CustID = t.CustID更改为x.InvoiceID = t.InvoiceID