update # set @1=case when @2<>flag2 then isnull(sl,isnull(结存数量,0)) else @1+isnull(sl,0) end,结存数量=@1 ,@2=flag2 where flag1 in (3,4,2)这条语句不知道怎样改那能满 足上面的要求。select * into # from(
select distinct cast(ybbm as varchar(10)) ybbm,null 单号,null sl, NULL 结存数量,结存金额,1 flag1,ybbm flag2 from @cgshdmxb
union all
select distinct '期初',null,null,null,Null,2,ybbm from @cgshdmxb
union all --这里改了一下
select null,csdh,sl,sl*dj as 结存金额,0,3,ybbm from @cgshdmxb,@cgshd
where @[email protected]
and @cgshd.增减标记=0
Union all
select null,csdh,sl,sl*dj as 结存金额,0,3,ybbm from @cgshdmxb,@cgshd
where @[email protected]
and @cgshd.增减标记=1
union all select distinct '期末',null,null,Null,0,4,ybbm from @cgshdmxb,@cgshd
union all
select distinct null,null,null,null,Null,5,ybbm from @cgshdmxb
) aa order by flag2,flag1
select distinct cast(ybbm as varchar(10)) ybbm,null 单号,null sl, NULL 结存数量,结存金额,1 flag1,ybbm flag2 from @cgshdmxb
union all
select distinct '期初',null,null,null,Null,2,ybbm from @cgshdmxb
union all --这里改了一下
select null,csdh,sl,sl*dj as 结存金额,0,3,ybbm from @cgshdmxb,@cgshd
where @[email protected]
and @cgshd.增减标记=0
Union all
select null,csdh,sl,sl*dj as 结存金额,0,3,ybbm from @cgshdmxb,@cgshd
where @[email protected]
and @cgshd.增减标记=1
union all select distinct '期末',null,null,Null,0,4,ybbm from @cgshdmxb,@cgshd
union all
select distinct null,null,null,null,Null,5,ybbm from @cgshdmxb
) aa order by flag2,flag1
select distinct cast(ybbm as varchar(10)) ybbm,null 单号,null 增加数量,null as 增加金额,Null 减少数量, NULL 结存数量,结存金额,1 flag1,ybbm flag2 from @cgshdmxb
union all
select distinct '期初',null,null,Null,NULL,null,Null,2,ybbm from @cgshdmxb
union all --这里改了一下
select null,csdh,sl 增加数量,sl*dj as 增加金额,Null,Null,sl*dl as 结存金额0,3,ybbm from @cgshdmxb,@cgshd
where @[email protected]
and @cgshd.增减标记=增
Union all
select null,csdh,Null,Null,sl 减少数量,减少金额,sl*dj as 结存金额,0,3,ybbm from @cgshdmxb,@cgshd
where @[email protected]
and @cgshd.增减标记=减
union all select distinct '期末',null,null,Null,Null,Null,Null,0,4,ybbm from @cgshdmxb,@cgshd
union all
select distinct null,null,null,null,Null,5,ybbm from @cgshdmxb
) aa order by flag2,flag1update # set @1=case when @2<>flag2 then isnull(sl,isnull(结存数量,0)) else @1+isnull(sl,0) end,结存数量=@1 ,@2=flag2 where flag1 in (3,4,2)这是我写的。但有些不对.
declare @cgshdmxb table (csdh int,ybbm int,sl int,dj float)insert @cgshd values(1,'2003-03-21',0) --0代表增,1代减
insert @cgshd values(2,'2003-03-22',1)
insert @cgshd values(3,'2003-03-23',1)
insert @cgshd values(4,'2003-03-25',0)
insert @cgshd values(5,'2003-03-22',0)
insert @cgshd values(6,'2003-03-22',1)
insert @cgshdmxb values(1,101,1,4) --101代表货品1 对于主表是增
insert @cgshdmxb values(2, 101,1,4) --对于主表是减
insert @cgshdmxb values(3, 101,1,4)--对于主表是减
insert @cgshdmxb values(4, 101,1,4)--对于主表是增
insert @cgshdmxb values(5, 101,1,4)--对于主表是增
insert @cgshdmxb values(6, 101,1,4)--对于主表是减insert @cgshdmxb values(1, 102 ,1, 3)--对于主表是增
insert @cgshdmxb values(2,102, 1,6)--对于主表是减select * into # from (
select distinct cast(ybbm as varchar(10)) ybbm,null 增加数量,null 增加金额,null 减少数量,null 减少金额, NULL 库存总数量,null 库存总金额,1 flag1,ybbm flag2 from @cgshdmxb
union all
select distinct '期初',null,null,null,null,1.0,4.0,2,ybbm from @cgshdmxb
union all
select null,case b.增减标记 when 0 then a.sl end,case b.增减标记 when 0 then a.dj end,case b.增减标记 when 1 then a.sl end,case b.增减标记 when 1 then a.dj end,0,0,3,a.ybbm from @cgshdmxb a,@cgshd b where a.csdh=b.csdh
union all
select distinct '期末',null,null,null,Null,null,null,4,ybbm from @cgshdmxb
union all
select distinct null,null,null,null,Null,null,null,5,ybbm from @cgshdmxb
) tem
order by flag2,flag1declare @1 int,@2 int,@3 float
select @1=0,@2=0,@3=0.0update # set @1=case when @2<>flag2 then isnull(库存总数量,0) else @1+isnull(增加数量,0)-isnull(减少数量,0) end,@3=case when @2<>flag2 then isnull(库存总金额,0) else @3+isnull(增加金额,0)-isnull(减少金额,0) end,@2=flag2,库存总金额=@3,库存总数量=@1 from # where flag1 in (3,4,2)
select * from #drop table #
101 NULL NULL NULL 1 101
期初 NULL NULL 5 2 101
NULL 1 10 15 3 101
NULL 1 5 20 3 101
NULL 2 6 26 3 101
期末 NULL NULL 26 4 101
NULL NULL NULL NULL 5 101
102 NULL NULL NULL 1 102
期初 NULL NULL 5 2 102
NULL 3 2 7 3 102
NULL 3 5 12 3 102
期末 NULL NULL 12 4 102
NULL NULL NULL NULL 5 102
这样的结果用以下语句
declare @1 int,@2 int
set @1=0
set @2=0update #t1 set @1=case when @2<>flag2 then isnull(sl,0) else @1+isnull(sl,0) end,结存数量=@1,@1=@1+结存数量,
@2=flag2 where flag1 in (2,3,4)
select * from #t1即可那天我给你短消息收到了么,有没有试试那个方法