11,849
社区成员
发帖
与我相关
我的任务
分享
SELECT A.ID,
A.TRANS_AMOUNT,
ISNULL(SUB_TOTAL,0)+CASE WHEN TYPE='存款' THEN TRANS_AMOUNT ELSE -1*TRANS_AMOUNT END AS BALANCE_FINAL,
TYPE
FROM #T A
OUTER APPLY
(SELECT SUM(CASE WHEN TYPE='存款' THEN TRANS_AMOUNT ELSE -1*TRANS_AMOUNT END) AS SUB_TOTAL FROM #T WHERE ID<A.ID) AS B
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(ID INT,[交易金额] int,[余额] int,[类型] nvarchar(20))
INSERT INTO t VALUES (1,100,100,'存款')
INSERT INTO t VALUES (2,20,0,'扣款')
INSERT INTO t VALUES (3,30,0,'扣款')
INSERT INTO t VALUES (4,10,0,'扣款')
INSERT INTO t VALUES (5,50,90,'存款')
SELECT ID
,[交易金额]
,SUM(CASE WHEN [类型]='存款' then 1 else -1 end * [交易金额]) over(order by id) as [余额]
,[类型]
FROM t
/*
ID 交易金额 余额 类型
1 100 100 存款
2 20 80 扣款
3 30 50 扣款
4 10 40 扣款
5 50 90 存款
*/
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab(
id int,
交易金额 int,
余额 int,
类型 varchar(20)
)
insert into #tab select 1,100,100,'存款'
insert into #tab select 2,20,0,'扣款'
insert into #tab select 3,30,0,'扣款'
insert into #tab select 4,10,0,'扣款'
insert into #tab select 5,50,90,'存款'
;WITH cte AS (
SELECT id,交易金额,余额,类型 FROM #tab where id=1
UNION ALL
SELECT a.id,a.交易金额, case a.类型 when '存款' then b.余额+a.交易金额 else b.余额-a.交易金额 end ,a.类型 from #tab a
INNER JOIN cte b ON a.id=b.id+1
)
SELECT * FROM cte
id 交易金额 余额 类型
----------- ----------- ----------- --------------------
1 100 100 存款
2 20 80 扣款
3 30 50 扣款
4 10 40 扣款
5 50 90 存款
(5 行受影响)