sql - 从行和列进行计算(将两列与不同行的第三列相加)

Question

引用我之前的问题 Adding columns resulting from GROUP BY clause

SELECT AcctId,Date,
   Sum(CASE
         WHEN DC = 'C' THEN TrnAmt
         ELSE 0
       END) AS C,
   Sum(CASE
         WHEN DC = 'D' THEN TrnAmt
         ELSE 0
       END) AS D
FROM   Table1 where AcctId = '51'
GROUP  BY AcctId,Date
ORDER  BY AcctId,Date

我执行了上面的查询并得到了我想要的结果..

  AcctId       Date         C        D
    51       2012-12-04   15000      0
    51       2012-12-05   150000  160596
    51       2012-12-06    600        0

现在我要对同一个查询执行另一个操作，即

我需要这样的结果

  AcctId   Date                                Result

    51       2012-12-04    (15000-0)->       15000  
    51       2012-12-05   (150000-160596) + (15000->The first value)  4404
    51       2012-12-06     600-0         +(4404 ->The calculated 2nd value) 5004

是否可以使用相同的查询？？。

Answer 1

使用递归 CTE

;WITH cte AS
 (
  SELECT AcctId, Date,
     Sum(CASE
           WHEN DC = 'C' THEN TrnAmt
           ELSE 0
         END) AS C,
     Sum(CASE
           WHEN DC = 'D' THEN TrnAmt
           ELSE 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 上的简单示例