我已经把期初,以及本期收入,本期收出放在一个表中了,表A(tblA)结构如下:
noid fType fQty fAmount
1 1 30 300
2 2 20 200
3 2 30 310
4 3 25 ?
5 3 20 ?
6 2 10 130
7 3 9 ?根据noid 从上至下开始计算?处的值 ?的值等于其上面的记录fType= 1+2-3的值,如何来实现?
noid fType fQty fAmount
1 1 30 300
2 2 20 200
3 2 30 310
4 3 25 ?
5 3 20 ?
6 2 10 130
7 3 9 ?根据noid 从上至下开始计算?处的值 ?的值等于其上面的记录fType= 1+2-3的值,如何来实现?
insert @t
select 1, 1, 30, 300 union all
select 2, 2, 20, 200 union all
select 3, 2, 30, 310 union all
select 4, 3, 25, NULL union all
select 5, 3, 20, NULL union all
select 6, 2, 10, 130 union all
select 7, 3, 9, NULL----更新
UPDATE a SET fAmount =
(select sum(case when fType = 1 or fType = 2 then fQty when fType = 3 then -fQty else 0 end)
from @t where noid <= a.noid)
FROM @t as a WHERE fType = 3
----查看
select * from @t/*结果
noid fType fQty fAmount
----------- ----------- ----------- -----------
1 1 30 300
2 2 20 200
3 2 30 310
4 3 25 55
5 3 20 35
6 2 10 130
7 3 9 36
*/
noid fType fQty fAmount
1 1 30 300
2 2 20 200
3 2 30 310
4 3 25 ?
5 3 20 ?
6 2 10 130
7 3 9 ?现在是要计算fType=3 的记录 fAmount的值,
fType=3的 fAmount值=其上面的记录 (fType=1的 fAmount + fType=2 的fAmount - fType=3 的fAmount)/(fType=1的 fqty + fType=2 的fqty - fType=3 的fqty)
insert @t
select 1, 1, 30, 300 union all
select 2, 2, 20, 200 union all
select 3, 2, 30, 310 union all
select 4, 3, 25, NULL union all
select 5, 3, 20, NULL union all
select 6, 2, 10, 130 union all
select 7, 3, 9, NULL----更新
UPDATE a SET fAmount =
fqty*((select
sum(case
when fType = 1 or fType = 2 then isnull(famount,0)
when fType = 3 then -isnull(famount,0) else 0
end)/
sum(case
when fType = 1 or fType = 2 then isnull(fqty,0)
when fType = 3 then -isnull(fqty,0) else 0
end)
from @t where noid <= a.noid))
FROM @t as a WHERE fType = 3
----查看
select * from @t
noid fType fQty fAmount
----------- ----------- ----------- -----------
1 1 30 300
2 2 20 200
3 2 30 310
4 3 25 350
5 3 20 460
6 2 10 130
7 3 9 234
*/