34,588
社区成员
发帖
与我相关
我的任务
分享
;WITH t(d,t,q,c,sq,sc)AS(
SELECT '2016-1-1','init',1,100,10,100 UNION
SELECT '2016-1-3','out',1,100,9,100 UNION
SELECT '2016-1-4','in',1,80,90,100
),tmp AS(
SELECT *
,SUM(t.q)OVER(PARTITION BY CASE WHEN t.t='init' THEN 'in' ELSE t.t END ORDER BY t.d) AS CurrentQty
,SUM(t.q*t.c)OVER(PARTITION BY CASE WHEN t.t='init' THEN 'in' ELSE t.t END ORDER BY t.d) AS CurrentAmount
FROM t
)
SELECT *,tmp.CurrentAmount/tmp.CurrentQty FROM tmp ORDER BY tmp.d,tmp.t
d t q c sq sc CurrentQty CurrentAmount
-------- ---- ----------- ----------- ----------- ----------- ----------- ------------- -----------
2016-1-1 init 1 100 10 100 1 100 100
2016-1-3 out 1 100 9 100 1 100 100
2016-1-4 in 1 80 90 100 2 180 90