select [材料名]=a.MaterialName,
[入仓数量]=sum(isnull(b.Quantity,0)),
[入仓重量]=sum(isnull(b.Quantity,0)*isnull(b.Weight,0)),
[出仓数量]=sum(isnull(c.Quantity,0)),
[出仓重量]=sum(isnull(c.Quantity,0)*isnull(c.Weight,0)),
[现存数量]=sum(isnull(a.Quantity,0)+isnull(b.Quantity,0)-isnull(c.Quantity,0)),
[入仓数量]=sum(isnull(a.Quantity,0)*isnull(a.Weight,0)+isnull(b.Quantity,0)*isnull(b.Weight,0)-isnull(c.Quantity,0)*isnull(c.Weight,0)),
from MaterialStorage a
left join (
select a.*,b.instoredate
from materialinstorelist a
left join materialinstore b
on a.instoreid=b.instoreid
where InStoreDate between '2005.1.1' and '2005.1.3'
)b
on a.MaterialID=b.MaterialID
left join (
select a.*,b.OutStoreDate
from MaterialOutStoreList a
left join MaterialOutStore b
on a.instoreid=b.instoreid
where OutStoreDate between '2005.1.1' and '2005.1.3'
)c
on a.MaterialID=b.MaterialID
group by a.MaterialName
[入仓数量]=sum(isnull(b.Quantity,0)),
[入仓重量]=sum(isnull(b.Quantity,0)*isnull(b.Weight,0)),
[出仓数量]=sum(isnull(c.Quantity,0)),
[出仓重量]=sum(isnull(c.Quantity,0)*isnull(c.Weight,0)),
[现存数量]=sum(isnull(a.Quantity,0)+isnull(b.Quantity,0)-isnull(c.Quantity,0)),
[入仓数量]=sum(isnull(a.Quantity,0)*isnull(a.Weight,0)+isnull(b.Quantity,0)*isnull(b.Weight,0)-isnull(c.Quantity,0)*isnull(c.Weight,0)),
from MaterialStorage a
left join (
select a.*,b.instoredate
from materialinstorelist a
left join materialinstore b
on a.instoreid=b.instoreid
where InStoreDate between '2005.1.1' and '2005.1.3'
)b
on a.MaterialID=b.MaterialID
left join (
select a.*,b.OutStoreDate
from MaterialOutStoreList a
left join MaterialOutStore b
on a.instoreid=b.instoreid
where OutStoreDate between '2005.1.1' and '2005.1.3'
)c
on a.MaterialID=b.MaterialID
group by a.MaterialName
drop table MaterialStorage
if exists(select name from sysobjects where name=N'MaterialInStore' and type='U')
drop table MaterialInStore
if exists(select name from sysobjects where name=N'MaterialInStoreList' and type='U')
drop table MaterialInStoreList
if exists(select name from sysobjects where name=N'MaterialOutStore' and type='U')
drop table MaterialOutStore
if exists(select name from sysobjects where name=N'MaterialOutStoreList' and type='U')
drop table MaterialOutStoreListcreate table MaterialStorage
(
MaterialID varchar(20),
MaterialName varchar(20),
Quantity varchar(20),
Weight int
)create table MaterialInStore
(
InstoreID varchar(20),
InStoreDate varchar(20)
)create table MaterialInStoreList
(
InStoreListID varchar(20),
InStoreID varchar(20),
MaterialID varchar(20),
Quantity int,
Weight int
)create table MaterialOutStore
(
OutstoreID varchar(20),
OutStoreDate varchar(20)
)create table MaterialOutStoreList
(
OutStoreListID varchar(20),
OutStoreID varchar(20),
MaterialID varchar(20),
Quantity int,
Weight int
)insert into MaterialStorage
select '1', 'AA', 10, 11
union
select '2', 'BB', 20, 12
union
select '3', 'CC', 30, 13
union
select '4', 'DD', 40, 14insert into MaterialInStore
select '1', '2005.1.1'
union
select '2', '2005.1.2'
union
select '3', '2005.1.3'insert into MaterialInStoreList
select '1', '1', '1', 10, 1
union
select '2', '1', '2', 10, 2
union
select '3', '2', '1', 10, 1insert into MaterialOutStore
select '1', '2005.1.1'
union
select '2', '2005.1.2'
union
select '3', '2005.1.3'insert into MaterialOutStoreList
select '1', '1', '1', 10, 1
union
select '2', '1', '2', 10, 2
union
select '3', '2', '1', 10, 1goselect [材料名]=a.MaterialName,
[入仓数量]=sum(isnull(b.Quantity,0)),
[入仓重量]=sum(isnull(b.Quantity,0)*isnull(b.Weight,0)),
[出仓数量]=sum(isnull(c.Quantity,0)),
[出仓重量]=sum(isnull(c.Quantity,0)*isnull(c.Weight,0)),
[现存数量]=sum(isnull(a.Quantity,0)+isnull(b.Quantity,0)-isnull(c.Quantity,0)),
[现存重量]=sum(isnull(a.Quantity,0)*isnull(a.Weight,0)+isnull(b.Quantity,0)*isnull(b.Weight,0)-isnull(c.Quantity,0)*isnull(c.Weight,0))
from MaterialStorage a
left join (
select a.*,b.instoredate
from materialinstorelist a
left join materialinstore b
on a.instoreid=b.instoreid
where InStoreDate between '2005.1.1' and '2005.1.3'
)b
on a.MaterialID=b.MaterialID
left join (
select a.*,b.OutStoreDate
from MaterialOutStoreList a
left join MaterialOutStore b
on a.OutStoreId=b.OutStoreId
where OutStoreDate between '2005.1.1' and '2005.1.3'
)c
on c.MaterialID=b.MaterialID
group by a.MaterialName
godrop table MaterialStorage
drop table MaterialInStore
drop table MaterialInStoreList
drop table MaterialOutStore
drop table MaterialOutStoreList