sql - SQL Server OVER() 子句未按预期运行

Question

我正在使用该OVER()子句根据帐号和付款日期获取付款的总金额。然后，我从当前余额中减去该运行总计，以确定每次交易完成后的余额

SELECT
    no_,
    amount,
    SUM(amount) OVER(PARTITION BY no_ ORDER BY effectiveDate DESC) AS RunningTotal,
    balance - (SUM(amount) OVER(PARTITION BY no_ ORDER BY effectiveDate DESC)) + amount AS CalculatedBalance,
    balance
FROM 
    c
WHERE 
    status != 'closed'
ORDER BY 
    no_

它适用于正数，但是当金额字段为负数时，我会得到如下奇怪的输出：

所有负数都会发生这种情况，我检查了所有正数并且它们是正确的。我在网上查了一下，找不到 OVER() 不接受负数的原因

标签： sqlsql-server-2012window-functions

Answer 1

你为什么要反过来计算？我希望逻辑更像这样：

SELECT no_, amount,
       SUM(amount) OVER (PARTITION BY no_ ORDER BY effectiveDate ASC) AS RunningTotal,
       (balance + amount +
        SUM(amount) OVER (PARTITION BY no_ ORDER BY effectiveDate ASC)
       ) AS CalculatedBalance,
       balance
FROM c
WHERE status <> 'closed'
ORDER BY no_;

此外，因为balance是负数，所以您想要添加金额而不是减去它们。