c# - 如何在SQL中计算余额？

我有下表，但我不知道如何根据借方和贷方列计算如下的余额和状态列？

| ID |    AR_DATE |ACC_CODE  | ACC_NAME | DETAILS | DEBIT | CREDIT | BALANCE ?| STATUS?|
----------------------------------------------------------------------------------------
|  1 | 2013-04-10 |      101 |      A/R |    Kofi |   500 |      0 |     500  | Debit  |
|  2 | 2013-04-10 |      101 |      A/R |     AMA |     0 |    250 |     250  | Debit  |
|  3 | 2013-04-11 |      101 |      A/R |    Boss |     0 |     50 |     200  | Debit  |
|  4 | 2013-04-12 |      101 |      A/R |    Jhon |     0 |    300 |     100  | Credit |

最佳答案

假设SQL Server 2012：

SELECT ID,
       AR_DATE,
       ACC_CODE,
       ACC_NAME,
       DETAILS,
       DEBIT,
       CREDIT,
       SUM(DEBIT - CREDIT) OVER(ORDER BY AR_DATE ASC) AS BALANCE,
       CASE WHEN CREDIT > 0 THEN 'Credit' ELSE 'Debit' END AS STATUS,
FROM [WhateverTable]

当您没有2012年时，您需要将其构建起来更加复杂：（source）

SELECT ID,
       AR_DATE,
       ACC_CODE,
       ACC_NAME,
       DETAILS,
       DEBIT,
       CREDIT,
       (SELECT SUM(b.DEBIT - b.CREDIT)
        FROM [WhateverTable] b
        WHERE b.AR_DATE <= a.AR_DATE) AS BALANCE,
       CASE WHEN CREDIT > 0 THEN 'Credit' ELSE 'Debit' END AS STATUS,
FROM [WhateverTable] a