有进库表 商品名称(ID),数量
商品名称(ID),数量
A 20
B 20
C 40
A 40
B 50
C 60
出库表 商品名称(ID),数量。
商品名称(ID),数量
A 10
B 10
C 30
A 30
B 40
C 50
求
商品名称(ID) 进货记录 出货记录 库存
A 60 40 20
B 70 50 20
C 100 80 20
商品名称(ID),数量
A 20
B 20
C 40
A 40
B 50
C 60
出库表 商品名称(ID),数量。
商品名称(ID),数量
A 10
B 10
C 30
A 30
B 40
C 50
求
商品名称(ID) 进货记录 出货记录 库存
A 60 40 20
B 70 50 20
C 100 80 20
insert into A values('A' , 20 )
insert into A values('B' , 20 )
insert into A values('C' , 40 )
insert into A values('A' , 40 )
insert into A values('B' , 50 )
insert into A values('C' , 60 )
create table B(ID varchar(10) , cnt int)
insert into B values('A' , 10 )
insert into B values('B' , 10 )
insert into B values('C' , 30 )
insert into B values('A' , 30 )
insert into B values('B' , 40 )
insert into B values('C' , 50 )
goselect a.id , sum(cnt) 进货记录,
isnull((select sum(cnt) from b where b.id = a.id),0) 出货记录,
isnull((select sum(cnt) from a t where t.id = a.id),0) - isnull((select sum(cnt) from b where b.id = a.id),0) 库存
from a
group by a.iddrop table a , b/*
id 进货记录 出货记录 库存
---------- ----------- ----------- -----------
A 60 40 20
B 70 50 20
C 100 80 20(所影响的行数为 3 行)*/
from
(
select ID,sum(数量 ) as n
from 进库表
group ID
)T1,
(
select ID,sum(数量) as n
from 出库表
group ID
)T2
where T1.id = T2.id
go
create table tb1(spid varchar(20),shl int)
go
insert tb1
select
'A', 20 union all select
'B' , 20 union all select
'C' , 40 union all select
'A' , 40 union all select
'B' , 50 union all select
'C' , 60
if OBJECT_ID('tb2') is not null drop table tb2
go
create table tb2(spid varchar(20),shl int)
go
insert tb2
select
'A', 10 union all select
'B' , 10 union all select
'C' , 30 union all select
'A' , 30 union all select
'B' , 40 union all select
'C' , 50 select a.spid,a.shl as 进货记录,b.shl as 出货记录,a.shl-b.shl as 库存数量
from(
select spid,SUM(shl) as shl from tb1 group by spid)a,
(select spid,SUM(shl) as shl from tb2 group by spid)b
where a.spid=b.spid/*
spid 进货记录 出货记录 库存数量
A 60 40 20
B 70 50 20
C 100 80 20*/
create table A(ID varchar(10) , cnt int)
insert into A values('A' , 20 )
insert into A values('B' , 20 )
insert into A values('C' , 40 )
insert into A values('A' , 40 )
insert into A values('B' , 50 )
insert into A values('C' , 60 )
create table B(ID varchar(10) , cnt int)
insert into B values('A' , 10 )
insert into B values('B' , 10 )
insert into B values('C' , 30 )
insert into B values('A' , 30 )
insert into B values('B' , 40 )
insert into B values('C' , 50 )
go
select a.id,sum(cnt) as 进货记录,max(出货记录)as 出货记录,库存=sum(cnt)-max(出货记录) from a
full join(
select id,sum(cnt) as 出货记录 from b group by id)b
on a.id=b.id
group by a.id
id 进货记录 出货记录 库存
---------- ----------- ----------- -----------
A 60 40 20
B 70 50 20
C 100 80 20(3 行受影响)drop table a
drop table b