SQL2008使用with求余额表，流水账方式

1、先准备数据，将要求余额的表数据插入临时表

SELECT Serial, VoucherNum, SubjectNum, SubjectName, Direction, Amount 
INTO #T1 FROM VoucherContent 
WHERE serial='SR201609' AND SubjectNum='5001070110'

数据明细图

SELECT * FROM #T1

2、使用with方式求余额，使用加借减贷方式

WITH etc AS
(
 SELECT row_number() OVER(ORDER BY Serial, VoucherNum, Direction) AS rindex,Serial, VoucherNum, Direction, SubjectNum, SubjectName, Amount
 FROM #T1
)
SELECT t.*
,ye=(SELECT sum(CASE WHEN Direction='借' THEN Amount ELSE 0-Amount end) FROM etc n1 WHERE n1.rindex<=t.rindex) 
FROM etc AS t
ORDER BY rindex

DROP TABLE #T1

如图

转载于:https://www.cnblogs.com/hdl217/p/8182510.html