27,579
社区成员
发帖
与我相关
我的任务
分享
create table #tb (rq datetime,czlx varchar(10),rk int,ck int)
insert into #tb values('2008-4-1 16:06','领用出库',null,100)
insert into #tb values('2008-4-3 14:53','采购进货',200,null)
insert into #tb values('2008-4-3 14:54','领用退库',null,-200)
insert into #tb values('2008-4-4 14:54','调入',200,null)
insert into #tb values('2008-4-5 14:54','采购退货',-100,null)
insert into #tb values('2008-4-6 14:54','调出',null,100)
declare @jy int
set @jy=100
select rq=convert(varchar(20),rq,120),czlx,rk,ck,jy=(select sum(isnull(rk,0))-sum(isnull(ck,0)) from #tb where rq<=a.rq)+@jy from #tb a
union all
select '合计','',sum(isnull(rk,0)),sum(isnull(ck,0)),sum(isnull(rk,0))-sum(isnull(ck,0))+@jy from #tb
rq czlx rk ck jy
-------------------- ---------- ----------- ----------- -----------
2008-04-01 16:06:00 领用出库 NULL 100 0
2008-04-03 14:53:00 采购进货 200 NULL 200
2008-04-03 14:54:00 领用退库 NULL -200 400
2008-04-04 14:54:00 调入 200 NULL 600
2008-04-05 14:54:00 采购退货 -100 NULL 500
2008-04-06 14:54:00 调出 NULL 100 400
合计 300 0 400
(7 行受影响)