参考我之前的问题添加 GROUP BY 子句产生的列 https://stackoverflow.com/questions/13892128/adding-columns-resulting-from-group-by-clause/13892514#13892514
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
是否可以使用相同的查询?
使用递归 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 http://sqlfiddle.com/#!3/45911/1/0
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)