不好意思!declare @cgshd table (csdh int,csdate datetime) insert @cgshd values(1,'2003-03-21') insert @cgshd values(2,'2003-03-22') declare @cgshdmxb table (csdh int,ybbm int,sl int) insert @cgshdmxb values(1,101,10) insert @cgshdmxb values(1, 101 ,5) insert @cgshdmxb values(2, 101 , 6) insert @cgshdmxb values(1,102,10) insert @cgshdmxb values(1, 102 ,5) insert @cgshdmxb values(2, 102 , 6)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,5,0,ybbm from @cgshdmxb union all select null,csdh,sl,0,3,ybbm from @cgshdmxb union all select distinct '期末',null,null,0,4,ybbm from @cgshdmxb union all select distinct null,null,null,null,5,ybbm from @cgshdmxb ) aa order by flag2,flag1declare @1 int,@2 int set @1=0 set @2=0update # set @1=case when @2<>flag2 then isnull(sl,isnull(结存数量,0)) else @1+isnull(sl,0) end,结存数量=@1 ,@2=flag2 where flag1 in (0,3,4,2) select ybbm,单号,sl,结存数量 from #drop table #
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.增减标记=增 Union all select null,csdh,sl,sl*dj as 结存金额,0,3,ybbm from @cgshdmxb,@cgshd where @[email protected] and @cgshd.增减标记=减 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 另外我加了个金额统计
101 是货品1的编号 102 是货品2的编号 在上面的结果是我连接另外一个表得到的类型=(select 货品名称 from hb where [email protected])最后union 上的你现在看懂我的意思了吗?
是这个意思吗??declare @cgshd table (csdh int,csdate datetime,增减标记 bit) 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 #
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 这里你简写了是不是?如果数改一下 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,2,4)--对于主表是增 insert @cgshdmxb values(5, 101,2,4)--对于主表是增 insert @cgshdmxb values(6, 101,2,4)--对于主表是减那结果就不对了。应该是不是 dj*sl
我原来用两个 union all 你给合起来了。 update # 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)还可以这样写呀。又学了一招。
insert @cgshd values(1,'2003-03-21')
insert @cgshd values(2,'2003-03-22')
declare @cgshdmxb table (csdh int,ybbm int,sl int)
insert @cgshdmxb values(1,101,10)
insert @cgshdmxb values(1, 101 ,5)
insert @cgshdmxb values(2, 101 , 6)
insert @cgshdmxb values(1,102,10)
insert @cgshdmxb values(1, 102 ,5)
insert @cgshdmxb values(2, 102 , 6)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,5,0,ybbm from @cgshdmxb
union all
select null,csdh,sl,0,3,ybbm from @cgshdmxb
union all
select distinct '期末',null,null,0,4,ybbm from @cgshdmxb
union all
select distinct null,null,null,null,5,ybbm from @cgshdmxb
) aa order by flag2,flag1declare @1 int,@2 int
set @1=0
set @2=0update # set @1=case when @2<>flag2 then isnull(sl,isnull(结存数量,0)) else @1+isnull(sl,0) end,结存数量=@1 ,@2=flag2 where flag1 in (0,3,4,2)
select ybbm,单号,sl,结存数量 from #drop table #
declare @cgshdmxb table (csdh int,ybbm int,sl int)这个表结构没变.
标题 增加数量 增加金额 减少数量 减少金额 库存总数量 库存总金额
货品1 NULL NULL NULL NULL NULL NULL
期初 NULL NULL NULL NULL 1.0 4.0
NULL 1.0 4.0 4.0 NULL 2.0 8.0
NULL NULL NULL 1.0 4.0 1.0 4.0
NULL NULL NULL 1.0 4.0 0 0
NULL 1.0 4.0 NULL NULL 1.0 4.0
NULL 1.0 4.0 NULL NULL 2.0 8.0
NULL NULL NULL 1.0 4.0 4.0 4.0
期末 NULL NULL NULL NULL 4.0 4
NULL NULL NULL NULL NULL NULL NULL
货品2 NULL NULL NULL NULL NULL NULL
期初 NULL NULL NULL NULL 1.0 5
NULL 1.0 3.0 NULL NULL 2.0 8
NULL NULL NULL 1.0 6 1.0 2
期末 NULL NULL NULL NULL 1 2
上面是一个结果。要得到这个结果该怎样做。
不知道像是否看懂了我的意思和表的结构。
我在改你那个case 语句很难理解。 我要判断是增还是减。
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.增减标记=增
Union all
select null,csdh,sl,sl*dj as 结存金额,0,3,ybbm from @cgshdmxb,@cgshd
where @[email protected]
and @cgshd.增减标记=减
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
另外我加了个金额统计
货品1 NULL NULL NULL NULL NULL NULL
期初 NULL NULL NULL NULL 1.0 4.0
NULL 1.0 4.0 Null NULL 2.0 8.0 --刚才这行有个错
NULL NULL NULL 1.0 4.0 1.0 4.0
NULL NULL NULL 1.0 4.0 0 0
NULL 1.0 4.0 NULL NULL 1.0 4.0
NULL 1.0 4.0 NULL NULL 2.0 8.0
NULL NULL NULL 1.0 4.0 4.0 4.0
期末 NULL NULL NULL NULL 4.0 4
NULL NULL NULL NULL NULL NULL NULL
货品2 NULL NULL NULL NULL NULL NULL
期初 NULL NULL NULL NULL 1.0 5
NULL 1.0 3.0 NULL NULL 2.0 8
NULL NULL NULL 1.0 6 1.0 2
期末 NULL NULL NULL NULL 1 2
insert @cgshd values(1,'2003-03-21','增')
insert @cgshd values(2,'2003-03-22','减')declare @cgshdmxb table (csdh int,ybbm int,sl int)
insert @cgshdmxb values(1,101,10)
insert @cgshdmxb values(1,101 ,5)
insert @cgshdmxb values(2, 101 , 6)
insert @cgshdmxb values(1,102,10)
insert @cgshdmxb values(1, 102 ,5)
insert @cgshdmxb values(2, 102 , 6)这个源吗??怎么看不出规律??
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)
明细表数据
csdh, ybbm,sl,dj(单价)
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)--对于主表是减@cgshd为 1的的单据明细记录
有两条,ybbm 为101,102
为2的单据明细记录有两条
101,102其它的单据各一条。
102 是货品2的编号
在上面的结果是我连接另外一个表得到的类型=(select 货品名称 from hb where [email protected])最后union 上的你现在看懂我的意思了吗?
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 #
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
这里你简写了是不是?如果数改一下
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,2,4)--对于主表是增
insert @cgshdmxb values(5, 101,2,4)--对于主表是增
insert @cgshdmxb values(6, 101,2,4)--对于主表是减那结果就不对了。应该是不是 dj*sl
union all
你给合起来了。
update # 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)还可以这样写呀。又学了一招。