34,591
社区成员
发帖
与我相关
我的任务
分享移动加权平均法的SQL,大家看看如何来写?
我已经把期初,以及本期收入,本期收出放在一个表中了,表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的值,如何来实现?
...全文
请发表友善的回复…
发表回复
sp4 2007-06-29
- 打赏
- 举报
应该没有问题啊
xuehf 2007-06-28
- 打赏
- 举报
第4行的结果好像不对的
hellowork 2007-06-28
- 打赏
- 举报
/*结果
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
*/
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
*/
hellowork 2007-06-28
- 打赏
- 举报
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
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
肥胖的柠檬 2007-06-28
- 打赏
- 举报
4 3 25 ? --这个是不是? 190/20*25
xuehf 2007-06-28
- 打赏
- 举报
我已经把期初,以及本期收入,本期收出放在一个表中了,表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)
结构如下:
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)
肥胖的柠檬 2007-06-28
- 打赏
- 举报
刚才我写错了, fType=3 处 fAMount=fqty* (其前面的记录 famount(1+2-3)/fqty(1+2-3))
??????这是什么意思????
??????这是什么意思????
w75251455 2007-06-28
- 打赏
- 举报
你把你要的结果写下看看！！！
xuehf 2007-06-28
- 打赏
- 举报
一两清风帮忙再看看
xuehf 2007-06-28
- 打赏
- 举报
刚才我写错了, fType=3 处 fAMount=fqty* (其前面的记录 famount(1+2-3)/fqty(1+2-3))
hellowork 2007-06-28
- 打赏
- 举报
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
*/
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 2007-06-28
- 打赏
- 举报
快来帮忙呀
nic7968 2007-06-28
- 打赏
- 举报
ding
