如何在SQL Server中计算余额

Question

我有一个查询，结果是

Code      id    Date        Amount  Paid      Balance
------------------------------------------------------
INV-000001  0   10/25/2019  50000   420       0
INV-000001  0   10/25/2019  50000   90        0
INV-000001  0   10/25/2019  50000   100       0
INV-000002  0   10/30/2019  25000   0         0
INV-000003  0   10/30/2019  25000   0         0
INV-000004  0   10/30/2019  25000   0         0

我想用SQL计算余额。例如

在第一行中，金额为50000，已支付420，因此余额为49580。第二行余额为50000-90-(第一行余额420(=49490

SELECT p.Code
,p.DATE
,p.STATUS
,'' AS Customer
,InvoiceAmount
,ISNULL(Paid, 0) AS Paid
,0 AS balance
FROM purchaseinvmaster p
LEFT OUTER JOIN InvoiceVoucherMap i ON i.InvoiceId = p.id
LEFT OUTER JOIN debitcreditmaster d ON i.VoucherId = d.id

Answer 1

SELECT  p.Code, 
InvoiceAmount , ISNULL(Paid,0) as Paid,
InvoiceAmount - SUM(ISNULL(Paid,0)) OVER (ORDER BY p.id ROWS UNBOUNDED PRECEDING) AS Balance
from purchaseinvmaster p

Answer 2

您没有提到您正在使用哪个数据库，但您可以使用标准窗口函数来计算余额。例如：

select
*,
amount - sum(paid) over(order by date) as balance
from t

Answer 3

基于基本查询中的ISNULL()，我假设您使用的是SQL Server，但这应该在大多数主要平台上进行一些小调整。

我进一步假设DATE列只是一个日期，而不是日期时间。这意味着我们需要对同一天发生的交易下达命令。为了做到这一点，我采用了您的基本查询，并将其封装在公共表表达式(CTE(中，并向结果中添加了一个简单的ROW-NUMBER()。您的一个表可能有一个列建议更好的顺序，但如果没有，这就可以了。它使用子句ORDER BY (SELECT 0)，因为窗口函数需要orderby，但我们没有可引用的排序列。

之后，在主查询中，使用带窗口的SUM来获取您的付款总额。

WITH cte AS
(
SELECT 
p.Code
,p.DATE
,p.STATUS
,'' AS Customer
,InvoiceAmount
,ISNULL(Paid, 0) AS Paid
,ROW_NUMBER() OVER (PARTITION BY p.Code ORDER BY (SELECT 0)) AS OrdinalNbr
FROM purchaseinvmaster p
LEFT OUTER JOIN InvoiceVoucherMap i
ON i.InvoiceId = p.id
LEFT OUTER JOIN debitcreditmaster d
ON i.VoucherId = d.id
)
SELECT 
Code
,DATE
,STATUS
,Customer
,InvoiceAmount
,Paid
,Balance = InvoiceAmount - (SUM(Paid) OVER (PARTITION BY Code ORDER BY OrdinalNbr))
FROM 
cte;