入库表rk
1、产品ID 2、入库数量
aaa 10
bbb 20
aaa 20
ccc 5
出库表ck
1、产品ID 2、出库数量
aaa 12
bbb 7查询后显示出如下数据产品ID 库存数
aaa 18
bbb 13
ccc 5
就是要按产品ID汇总后的库存总数,谢谢
1、产品ID 2、入库数量
aaa 10
bbb 20
aaa 20
ccc 5
出库表ck
1、产品ID 2、出库数量
aaa 12
bbb 7查询后显示出如下数据产品ID 库存数
aaa 18
bbb 13
ccc 5
就是要按产品ID汇总后的库存总数,谢谢
select 产品ID,sum(case flag when 'in' then isnull(数量,0) when 'out' then -isnull(数量,0) end) 库存
from(
select 产品ID,入库数量 as 数量,'in' as flag from rk
union all
select 产品ID,出库数量 as 数量,'out' as flag from ck
)t
group by 产品ID
select 产品ID,sum(数量*flag) 库存
from(
select 产品ID,入库数量 as 数量,1 as flag from rk
union all
select 产品ID,出库数量 as 数量,-1 as flag from ck
)t
group by 产品ID
(select 产品ID,入库数量=sum(入库数量) from rk group by 产品ID) a
left jion
(select 产品ID,出库数量=sum(出库数量) from ck group by 产品ID) b
on a.产品ID=b.产品ID
create table rk
(产品ID varchar(10),入库数量 int)
insert into rk
select 'aaa',10 union all
select 'bbb',20 union all
select 'aaa',20 union all
select 'ccc',5
gocreate table ck
(产品ID varchar(10),出库数量 int)
insert into ck
select 'aaa',12 union all
select 'bbb',7
goselect 产品ID,sum(数量*flag) 库存
from(
select 产品ID,入库数量 as 数量,1 as flag from rk
union all
select 产品ID,出库数量,-1 from ck
)t
group by 产品IDdrop table rk,ck/*************产品ID 库存
---------- -----------
aaa 18
bbb 13
ccc 5(3 行受影响)
产品ID,sum(数量*flag) as 库存
from
(
select 产品ID,入库数量 as 数量,1 as flag from rk
union all
select 产品ID,出库数量 as 数量,-1 as flag from ck
)t
group by
产品ID
from(
select 产品ID,入库数量 as 数量,1 as flag from rk
union all
select 产品ID,出库数量 as 数量,-1 as flag from ck
)t
group by 产品ID
(
select 产品id as id, 入库数量 as sl from rk
union all select 产品id as id, -出库数量 as sl from ck) a
group by id
select 产品id,sum(入库数量)as 入库数量,0 as 出库数量 from rk group by 产品id
union all
select 产品id,0 as 入库数量,sum(出库数量)as 出库数量 from ck group by 产品id
)aa
group by aa.产品id