sqlserver 2005，表#t1的数据如下：
rowid type debitamount creditamount balance
1 期初 5000
2 收入 200
3 收入 400
4 支出 120
5 收入 100
6 支出 200
我想写一个sql代码，实现blance=blance(期初)+debitamount-creditamount，如#t1变成：
rowid type debitamount creditamount balance
1 期初 5000
2 收入 200 5200
3 收入 400 5600
4 支出 120 5480
5 收入 100 5580
6 支出 200 5380
------解决思路----------------------
select tb1.rowid,sum(tb2.balance) as balance
from
(select rowid from t1) as tb1
inner join
(select rowid,isnull(balance,0)+isnull(debitamount,0)-isnull(creditamount,0) as balance from t1) as tb2
on tb1.rowid>=tb2.rowid
group by tb1.rowid
------解决思路----------------------
with tb (rowid,type,debitamount,creditamount,balance) as
(
select 1,'期初',null,null,5000 union all
select 2,'收入',200,null,null union all
select 3,'收入',400,null,null union all
select 4,'支出',null,120,null union all
select 5,'收入',100,null,null union all
select 6,'支出',null,200,null
),
tb1 as
(
select a.rowid,sum(case when b.type='期初' then b.balance when b.type='收入' then b.debitamount else -b.creditamount end)balance
from tb a,tb b
where a.rowid>=b.rowid group by a.rowid
)
select a.rowid,a.type,a.debitamount,a.creditamount,b.balance from tb a left join tb1 b on a.rowid=b.rowid
------解决思路----------------------
--一句就可以了
with tb (rowid,type,debitamount,creditamount,balance) as
(
select 1,'期初',null,null,5000 union all
select 2,'收入',200,null,null union all
select 3,'收入',400,null,null union all
select 4,'支出',null,120,null union all
select 5,'收入',100,null,null union all
select 6,'支出',null,200,null
)
select a.rowid,a.type,a.debitamount,a.creditamount,sum(case when b.type='期初' then b.balance when b.type='收入' then b.debitamount else -b.creditamount end)balance
from tb a,tb b
where a.rowid>=b.rowid group by a.rowid,a.type,a.debitamount,a.creditamount
------解决思路----------------------
SELECT
E.ROWID,
E.TYPE,
E.DEBITAMOUNT,
E.CREDITAMOUNT,
D.BLANCE
FROM
(
SELECT
MAX(rowid) AS ROWID,
SUM(BALANCE) AS BLANCE
FROM
(
SELECT A.ROWID AS ROWID_A, B.ROWID, B.TYPE, B.DEBITAMOUNT, B.CREDITAMOUNT, ISNULL(B.DEBITAMOUNT, 0)-ISNULL(B.CREDITAMOUNT, 0)+ISNULL(B.BALANCE, 0) AS BALANCE
FROM TEMP_TABLE1$ AS A
INNER JOIN TEMP_TABLE1$ AS B ON A.ROWID >= B.ROWID
) AS C
GROUP BY C.ROWID_A
) AS D
INNER JOIN TEMP_TABLE1$ AS E ON D.ROWID=E.ROWID