使用递归CTE
;WITH cte AS ( SELECt AcctId, Date, Sum(CASEWHEN DC = 'C' THEN TrnAmtELSE 0 END) AS C, Sum(CASEWHEN DC = 'D' THEN TrnAmtELSE 0 END) AS D, ROW_NUMBER() OVER (ORDER BY AcctId, Date) AS Id FROM Table1 where AcctId = '51' GROUP BY AcctId, Date ), cte2 AS ( SELECt Id, AcctId, Date, C, D, (C - 0) AS Result FROM cte WHERe Id = 1 UNIOn ALL SELECt c.Id, c.AcctId, c.Date, c.C, c.D, (c.C - c.D) + ct.Result FROM cte c JOIN cte2 ct ON c.Id = ct.Id + 1 ) SELECt * FROM cte2
SQLFiddle上的 简单示例
****
