select 库存货号,sum(库存数量) as 库存数量
from (select 进货货号 as 库存货号,进货数量 as 库存数量 from 进货表
union all
select 销售货号 as 库存货号,销售数量 as -库存数量 from 进货表
)
group by 库存货号
from (select 进货货号 as 库存货号,进货数量 as 库存数量 from 进货表
union all
select 销售货号 as 库存货号,销售数量 as -库存数量 from 进货表
)
group by 库存货号
from (select 进货货号 as 库存货号,进货数量 as 库存数量 from 进货表
union all
select 销售货号 as 库存货号,销售数量 as -库存数量 from 进货表
) aa
group by aa.库存货号
(
select jhhh, jhsl from 进货表
union all
select xshh, 0 - xssl from 销售表
) t group by jhhh
sum(isnull(t1.jhsl,0))-sum(isnull(t2.xssl,0)) kcsl
from 进货表 t1 full join 销售表 t2 on t1.jhhh=t2.xshh
group by isnull(t1.jhhh,t2.xshh)
select 库存货号,sum(库存数量) as 库存数量
from (select 进货货号 as 库存货号,进货数量 as 库存数量 from 进货表
union all
select 销售货号 as 库存货号,-销售数量 as 库存数量 from 进货表 --负号写错位置了
) aa
group by aa.库存货号
insert ta1 values('101',4)
insert ta1 values('102',5)
insert ta1 values('103',2)
create table ta2(销售货号 char(3),销售数量 int)
insert ta2 values('101',3)
insert ta2 values('103',2)
select 进货货号 as kchh,(进华数量-销售数量) as kcsl from (select a.进货货号,a.进华数量,isnull(b.销售数量,0) as 销售数量
from ta1 a left outer join ta2 b on a.进货货号=b.销售货号) as ckchh kcsl
---- -----------
101 1
102 5
103 0(所影响的行数为 3 行)
on a.jhhh=b.xshh
from y_xsqd where jhqd_hh=xsqd_hh and xsqd_xssl>0) as xssl from y_jhqd where (select isnull(sum(xsqd_xssl),0)
from y_xsqd where jhqd_hh=xsqd_hh and xsqd_xssl>0)>0) aa,(select distinct xsqd_hh,(select isnull(sum(jhqd_jhsl),0)
from y_jhqd where jhqd_hh=xsqd_hh) as jhsl from y_xsqd where (select isnull(sum(jhqd_jhsl),0) from y_jhqd where jhqd_hh=xsqd_hh)>0)
bb,y_jhqd cc where aa.jhqd_hh=cc.jhqd_hh and cc.jhqd_hh=bb.xsqd_hh and (bb.jhsl-aa.xssl)>0
唉,我的这个可怎么改……
select distinct cc.jhqd_hh,(bb.jhsl-aa.xssl) from (select distinct jhqd_hh,(select isnull(sum(xsqd_xssl),0)
from y_xsqd where jhqd_hh=xsqd_hh and xsqd_xssl>0) as xssl from y_jhqd where (select isnull(sum(xsqd_xssl),0)
from y_xsqd where jhqd_hh=xsqd_hh and xsqd_xssl>0)>0) aa,(select distinct xsqd_hh,(select isnull(sum(jhqd_jhsl),0)
from y_jhqd where jhqd_hh=xsqd_hh) as jhsl from y_xsqd where (select isnull(sum(jhqd_jhsl),0) from y_jhqd where jhqd_hh=xsqd_hh)>0)
bb,y_jhqd cc where aa.jhqd_hh=cc.jhqd_hh and cc.jhqd_hh=bb.xsqd_hh and (bb.jhsl-aa.xssl)>0
( jhhh char(3),
jhsl int)create table xs
( xshh char(3),
xssl int)create table kc
( kchh char(3),
kcsl int)insert into jh(jhhh,jhsl) values('101',4)
insert into jh(jhhh,jhsl) values('102',5)
insert into jh(jhhh,jhsl) values('103',9)
insert into jh(jhhh,jhsl) values('104',7)
insert into jh(jhhh,jhsl) values('101',9)insert into xs(xshh,xssl) values('101',3)
insert into xs(xshh,xssl) values('102',4)
insert into xs(xshh,xssl) values('103',6)select jhhh as hh,jhsl,xssl into #hw
from
(select jhhh,sum(jhsl) as jhsl from jh group by jhhh) a
left join
(select xshh,sum(xssl) as xssl from xs group by xshh) b
on a.jhhh=b.xshhupdate #hw set xssl=0 where xssl is nullinsert into kc
select hh,jhsl-xssl from #hw
from 进货表 left outer jion 销售表 on 进货表.进货库号=销售表.销售数量
from 进货表 left outer jion 销售表 on 进货表.进货库号=销售表.销售数量
group by 进货表.进货库号
(CASE dbo.jhb.jhsl WHEN dbo.xsb.xssl THEN dbo.jhb.jhsl - dbo.xsb.xssl ELSE dbo.jhb.jhsl
END) AS kcsl
FROM dbo.jhb LEFT OUTER JOIN
dbo.xsb ON dbo.jhb.jhhh = dbo.xsb.xshh