如何在sql 2008中计算以前的余额

Question

我想要在sql 2008中添加以下表格的前一个余额的代码。像

Balance= + Debit 
Balance= -Credit 



AccountReceivable 
+----+------------+----------+----------+----------+---------+-------+--------+---------+-----+ 
| ID | AR_Date | Ref_No | Acc_Code | Acc_Name | Details | Debit | Credit |Balance |  | 
+----+------------+----------+----------+----------+---------+-------+--------+---------+-----+ 
| 1 | 2013-04-10 |   |  101 | A/R  | Kofi | 500 | | 0 |  | 
| 2 | 2013-04-10 |   |  101 | A/R  | AMA |0  | | 250 |  | 
| 3 | 2013-04-11 | CH552778 |  101 | A/R  | Boss | 0  | | 50 |  | 
+----+------------+----------+----------+----------+---------+-------+--------+---------+-----+ 

Answer 1

结合ROW_NUMBER（）使用CTE。在此之后，您可以查询CTE并加入前一行的当前行号。

例如

; 
WITH CTE as (
SELECT 
    ac.*, 
     RN = ROW_NUMBER() OVER (ORDER BY ID) 
FROM 
    AccountsReceivable ac 

) 

SELECT 
cur.Balance + prev.Balance AS NewBalance 
FROM 
CTE cur 
LEFT OUTER JOIN CTE prev ON cur.RN = prev.RN + 1