22,207
社区成员
发帖
与我相关
我的任务
分享
with a(日期,ITEMID,数量) as (
select '3-1',1001,100 union all
select '3-2',1001,-80 union all
select '3-3',1001,-30 union all
select '3-4',1001,-50 union all
select '3-1',1002,60 union all
select '3-2',1002,-30 union all
select '3-3',1002,-50)
select a.*,SUM(b.数量) as 累计,case when SUM(b.数量)<0 then -1*SUM(b.数量) else 0 end as 需求
from a inner join a as b on a.ITEMID=b.ITEMID and a.日期>=b.日期
group by a.日期,a.ITEMID,a.数量 order by a.ITEMID,a.日期
/*
日期 itemid 数量 累计 需求
3-1 1001 100 100 0
3-2 1001 -80 20 0
3-3 1001 -30 -10 10
3-4 1001 -50 -60 60
3-1 1002 60 60 0
3-2 1002 -30 30 0
3-3 1002 -50 -20 20
*/