withdata(_index,add_amount,red_amount)as
(
select1,0,-3080unionall
select2,3003,0unionall
select3,0,-154unionall
select4,77,0unionall
select5,0,40unionall
select6,0,-1580unionall
select7,1540,0
)
--select*fromdata
selectdata.*,rest=(selectsum(add_amount)+sum(red_amount)fromdatadata2wheredata2._indexDECLARE@tTABLE(序号INTNOTNULL,增加DECIMAL(10,2)NOTNULL,减少DECIMAL(10,2)NOTNULL)
INSERT@t(序号,增加,减少)
VALUES(1,40557,-9736),(2,61185,-89882),(3,90955,-59306),
(4,65656,-79207),(5,22674,-21292),(6,84536,-72462),
(7,72849,-68889),(8,58352,-42120),(9,38306,-55301),
(10,7283,-27803),(11,80728,-34710),(12,58263,-82854),
(13,32959,-85673),(14,51437,-93619),(15,38617,-96284)
;WITHtAS(SELECT*,CONVERT(DECIMAL(10,2),增加+减少)余额FROM@tWHERE序号=1
UNIONALL
SELECTt2.*,
CONVERT(DECIMAL(10,2),t2.增加+t2.减少+t.余额)
FROM@tt2
INNERJOINtONt2.序号=t.序号+1)
SELECT*FROMt;
|
