从行和列计算问题(将两列与不同行的第三列相加) [英] Issue Calculating from Rows and Columns(Summing two columns with the third of a different row)

问题描述

参考我之前的问题添加由 GROUP BY 子句产生的列

SELECT AcctId,Date,总和(CASEWHEN DC = 'C' THEN TrnAmt其他 0完)作为 C，总和(CASEWHEN DC = 'D' THEN TrnAmt其他 0完)AS D从表 1 其中 AcctId = '51'GROUP BY AcctId,Date按帐户编号、日期排序

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

 AcctId 日期 C D51 2012-12-04 15000 051 2012-12-05 150000 16059651 2012-12-06 600 0

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

我需要这样的结果

 AcctId 日期 结果51 2012-12-04 (15000-0)->1500051 2012-12-05 (150000-160596) + (15000->第一个值) 440451 2012-12-06 600-0 +(4404 -> 计算出的第二个值) 5004

是否可以使用相同的查询??.

解决方案

使用递归 CTE

;with cte AS(选择帐户 ID、日期、总和(CASEWHEN DC = 'C' THEN TrnAmt其他 0完)作为 C，总和(CASEWHEN DC = 'D' THEN TrnAmt其他 0完)AS D，ROW_NUMBER() OVER (ORDER BY AcctId, Date) AS Id从表 1 其中 AcctId = '51'GROUP BY AcctId, 日期), cte2 AS(SELECT Id, AcctId, Date, C, D, (C - 0) AS 结果发件人Id = 1联合所有选择 c.Id, c.AcctId, c.Date, c.C, c.D, (c.C - c.D) + ct.Result从 cte c JOIN cte2 ct ON c.Id = ct.Id + 1)选择 *从 cte2

关于 SQLFiddle

With reference to my previous 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

I executed the above query and got my desired result..

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

now I have a another operation to do on the same query i.e.

I need the result to be like this

  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

Is it possible with the same query??.

解决方案

use recursive 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

Simple example on SQLFiddle

这篇关于从行和列计算问题(将两列与不同行的第三列相加)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！