移动加权平均法的SQL,大家看看如何来写?

[xuehf]

我已经把期初,以及本期收入,本期收出放在一个表中了,表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的值,如何来实现?

[nic7968]

ding

[xuehf]

快来帮忙呀

[hellowork]

declare @t table(noid int,fType int,fQty int,fAmount int)
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
*/

[xuehf]

刚才我写错了, fType=3 处 fAMount=fqty* (其前面的记录 famount(1+2-3)/fqty(1+2-3))

[xuehf]

一两清风帮忙再看看

[w75251455]

你把你要的结果写下看看！！！

[肥胖的柠檬]

刚才我写错了, fType=3 处 fAMount=fqty* (其前面的记录 famount(1+2-3)/fqty(1+2-3))

??????这是什么意思????

[xuehf]

我已经把期初,以及本期收入,本期收出放在一个表中了,表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 ?

现在是要计算fType=3 的记录 fAmount的值,
fType=3的 fAmount值=其上面的记录 (fType=1的 fAmount + fType=2 的fAmount - fType=3 的fAmount)/(fType=1的 fqty + fType=2 的fqty - fType=3 的fqty)

[肥胖的柠檬]

4 3 25 ? --这个是不是? 190/20*25

[hellowork]

declare @t table(noid int,fType int,fQty int,fAmount int)
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

[hellowork]

/*结果
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
*/

[xuehf]

第4行的结果好像不对的

[sp4]

应该没有问题啊