select isnull (a.产品id,b.产品id) as 产id,
isnull (a.入库数量,0)-isnull (b.出库数量,0) as 库存
from 表a a full join 表b b
on a.产品id=b.产品id
isnull (a.入库数量,0)-isnull (b.出库数量,0) as 库存
from 表a a full join 表b b
on a.产品id=b.产品id
调试欢乐多
,入库数量=sum(入库数量)
,出库数量=sum(出库数量)
,库存总数=sum(入库数量-出库数量)
from(
select 产品id,入库数量,出库数量=0 from 表a
union all
select 产品id,入库数量=0,出库数量 from 表b
)a group by 产品id
select isnull (a.产品id,b.产品id) as 产品id,
isnull ((a.a-b.b),a.a) as 库存数量 from
(select 产品id,sum(入库数量) as a from 表a group by 产品id) a full join
(select 产品id,sum(出库数量) as b from 表b group by 产品id) b
on a.产品id=b.产品id
create table 表a(id int,产品id int,入库数量 decimal(10,1))
insert 表a select 1,1,2.5
union all select 2,2,5.0
union all select 3,2,3.0
union all select 4,3,4.3create table 表b(id int,产品id int,出库数量 decimal(10,1))
insert 表b select 1,1,1.0
union all select 2,1,0.8
union all select 3,2,4.5
go--查询统计
select 产品id
,入库数量=sum(入库数量)
,出库数量=sum(出库数量)
,库存总数=sum(入库数量-出库数量)
from(
select 产品id,入库数量,出库数量=0 from 表a
union all
select 产品id,入库数量=0,出库数量 from 表b
)a group by 产品id
go--删除测试d
drop table 表a,表b/*--测试结果产品id 入库数量 出库数量 库存总数
----------- ----------- ---------- -------------
1 2.5 1.8 .7
2 8.0 4.5 3.5
3 4.3 .0 4.3(所影响的行数为 3 行)
--*/
,a.入库数量
,b.出库数量
,库存总数=(a.入库数量-isNull(b.出库数量,0))
from(Select 产品id ,入库数量=sum(入库数量) from 表a
group by 产品id )a left Join
(Select 产品id ,入库数量=sum(出库数量) from 表b
group by 产品id )b on a.产品id =b.产品id
,a.入库数量
,b.出库数量
,库存总数=(a.入库数量-isNull(b.出库数量,0))
from(Select 产品id ,入库数量=sum(入库数量) from 表a
group by 产品id )a left Join
(Select 产品id ,出库数量=sum(出库数量) from 表b
group by 产品id )b on a.产品id =b.产品id
create table 表a(id int,产品id int,入库数量 decimal(10,1))
insert 表a select 1,1,2.5
union all select 2,2,5.0
union all select 3,2,3.0
union all select 4,3,4.3create table 表b(id int,产品id int,出库数量 decimal(10,1))
insert 表b select 1,1,1.0
union all select 2,1,0.8
union all select 3,2,4.5
go--查询统计
select a.产品id
,isNull(a.入库数量,0)
,isNull(b.出库数量,0)
,库存总数=(isNull(a.入库数量,0)-isNull(b.出库数量,0))
from(Select 产品id ,入库数量=sum(isNull(入库数量,0)) from 表a
group by 产品id )a full Join
(Select 产品id ,出库数量=sum(isNull(出库数量,0)) from 表b
group by 产品id )b on a.产品id =b.产品id--删除测试d
drop table 表a,表b/*--测试结果产品id 入库数量 出库数量 库存总数
----------- ----------- ---------- -------------
1 2.5 1.8 .7
2 8.0 4.5 3.5
3 4.3 .0 4.3(所影响的行数为 3 行)
--*/
from 入库表A as a , 出库表B as b
where a.id=b.id
group by a.id