求出一段时间的库存历史记录的SQL语句

[jwing]

表StoreList(库存日志表)
日期 编号 仓库号 上期结存 本期收入 本期付出 本期结存
ID StoreDate PID STID LastStore ThisIN ThisOUT ThisStore
16 2005-12-30 00:00:00.000 P00001 001 0 100 0 100
17 2005-12-30 00:00:00.000 P00001 001 100 50 0 150
18 2005-12-31 00:00:00.000 P00001 001 150 30 0 180
19 2005-12-31 00:00:00.000 P00001 001 180 0 10 170

如何用SQL语句查出一段时间的各产品库存记录(如2005-12-30至2005-12-31)

编号 仓库号 上期结存 本期收入 本期付出 本期结存
PID STID LastStore ThisIN ThisOUT ThisStore
P00001 001 0 180 10 170

如果SQL语句不能实现,可以用什么方法实现?

[jwing]

表StoreList(库存日志表)
日期 编号 仓库号 上期结存 本期收入 本期付出 本期结存
ID StoreDate PID STID LastStore ThisIN ThisOUT ThisStore
16 2005-12-30 P00001 001 0 100 0 100
17 2005-12-30 P00001 001 100 50 0 150
18 2005-12-31 P00001 001 150 30 0 180
19 2005-12-31 P00001 001 180 0 10 170

如何用SQL语句查出一段时间的各产品库存记录(如2005-12-30至2005-12-31)

编号 仓库号 上期结存 本期收入 本期付出 本期结存
PID STID LastStore ThisIN ThisOUT ThisStore
P00001 001 0 180 10 170

如果SQL语句不能实现,可以用什么方法实现?

[jwing]

格式一发送就错位了,大家将就看吧,辛苦了!

[WangZWang]

select 编号,上期结存=(Select 上期结存 from StoreList where ID=min(a.ID))
本期收入=sum(本期收入),
本期付出=sum(本期付出),
本期结存=sum(本期收入)-sum(本期付出)+(Select 上期结存 from StoreList where ID=min(a.ID))
from StoreList as a
where StoreDate between '2005-12-30' and '2005-12-31'
group by 编号

[子陌红尘]

declare @StoreList table(
ID int,
StoreDate datetime,
PID varchar(10),
STID varchar(10),
LastStore int,
ThisIN int,
ThisOUT int,
ThisStore int)
insert into @storeList select 16,'2005-12-30','P00001','001',0 ,100,0 ,100
insert into @storeList select 17,'2005-12-30','P00001','001',100,50 ,0 ,150
insert into @storeList select 18,'2005-12-31','P00001','001',150,30 ,0 ,180
insert into @storeList select 19,'2005-12-31','P00001','001',180,0 ,10,170


declare @sdate datetime,@edate datetime
set @sdate = '2005-12-30'
set @edate = '2005-12-31'

select
编号 =a.PID,
仓库号 =a.STID,
上期结存= isnull((select top 1 ThisStore from @StoreList where PID=a.PID and STID=a.STID and StoreDate<@sdate order by StoreDate desc),0),
本期收入= isnull((select sum(ThisIN) from @StoreList where StoreDate between @sdate and @edate and PID=a.PID and STID=a.STID),0),
本期付出= isnull((select sum(ThisOUT) from @StoreList where StoreDate between @sdate and @edate and PID=a.PID and STID=a.STID),0),
本期结存= isnull((select top 1 ThisStore from @StoreList where PID=a.PID and STID=a.STID and StoreDate<@sdate order by StoreDate desc),0)
+isnull((select sum(ThisIN-ThisOUT) from @StoreList where StoreDate between @sdate and @edate and PID=a.PID and STID=a.STID),0)
from
@StoreList a
group by
a.PID,a.STID

/*
编号 仓库号 上期结存 本期收入 本期付出 本期结存
---------- ---------- ----------- ----------- ----------- -----------
P00001 001 0 180 10 170
*/

[WangZWang]

declare @StoreList table(
ID int,
StoreDate datetime,
PID varchar(10),
STID varchar(10),
LastStore int,
ThisIN int,
ThisOUT int,
ThisStore int)
insert into @storeList select 16,'2005-12-30','P00001','001',0 ,100,0 ,100
insert into @storeList select 17,'2005-12-30','P00001','001',100,50 ,0 ,150
insert into @storeList select 18,'2005-12-31','P00001','001',150,30 ,0 ,180
insert into @storeList select 19,'2005-12-31','P00001','001',180,0 ,10,170

--实现
select 编号=PID,仓库号=STID,
上期结存=(Select LastStore from @StoreList where ID=min(a.ID)),
本期收入=sum(ThisIN),
本期付出=sum(ThisOUT),
本期结存=sum(ThisIN)-sum(ThisOUT)+(Select LastStore
from @StoreList where ID=min(a.ID))
from @StoreList as a
where StoreDate between '2005-12-30' and '2005-12-31'
group by PID,STID

[mislrb]

select 编号=PID,
仓库号=STID,
上期结存=(Select LastStore from @StoreList where ID=min(a.ID)),
本期收入=sum(ThisIN),
本期付出=sum(ThisOUT),
本期结存=(Select LastStore from @StoreList where ID=max(a.ID))
from @StoreList as a
where StoreDate between '2005-12-30' and '2005-12-31'
group by PID,STID

[jwing]

问题解决了,多谢各位,送分!