我有一张表，字段为id,hpid,storage,date，意思是id，库存数和日期。
同一id的记录不是每天都有，现在我想查询出某段时间内的库存数量，该如何编写sql语句？
比如表内容如下：
id hpid storage date
1 1 1000 2012-10-10
2 1 2000 2012-10-12
3 1 5000 2012-10-13
4 1 5000 2012-10-14
5 1 4500 2012-10-18
6 1 5340 2012-10-22
7 1 684 2012-11-28
8 1 10000 2012-12-12
同一hpid，并不是每天都有记录，但是查询结果想要显示10~11月份每天的数据，如果当天没数据，则显示上条数据的记录。
搜索结果应该如下：
hpid storage date
1 0 2012-10-1
1 0 2012-10-2
1 0 2012-10-2
......
1 1000 2012-10-10
1 1000 2012-10-11
1 2000 2012-10-12
1 5000 2012-10-13
1 5000 2012-10-14
1 5000 2012-10-15
1 5000 2012-10-16
......
1 684 2012-11-30
想得到上述结果，想了很久不知如何编写sql语句，望大家指教！
------解决方案--------------------
declare @库存表 table(id int,hpid int,storage varchar(50),[date] date)
insert into @库存表
select 1,1,1000,'2012-10-10'
union all
select 2,1,2000,'2012-10-12'
union all
select 3,1,5000,'2012-10-13'
union all
select 4,1,5000,'2012-10-14'
union all
select 5,1,4500,'2012-10-18'
union all
select 6,1,5340,'2012-10-22'
union all
select 7,1,684,'2012-11-28'
union all
select 8,1,10000,'2012-12-12'
select
number
,hpid
,isnull(isnull(storage,
(
select top 1 storage from
(
select
d.number,h.hpid,s.storage,DATEADD(dd,d.number,'2012-10-01') [date]
from
(select * from master..spt_values where type='p' and number between 0 and (select DATEDIFF(dd,'2012-10-01','2012-11-30'))) d
cross join (select distinct hpid from @库存表) h
left join @库存表 s on h.hpid=s.hpid and DATEADD(dd,d.number,'2012-10-01')=s.date
) tt
where hpid=t.hpid and number<t.number and storage is not null order by number desc
))
,0) storage
,[date]
from
(
select
d.number,h.hpid,s.storage,DATEADD(dd,d.number,'2012-10-01') [date]
from
(select * from master..spt_values where type='p' and number between 0 and (select DATEDIFF(dd,'2012-10-01','2012-11-30'))) d
cross join (select distinct hpid from @库存表) h
left join @库存表 s on h.hpid=s.hpid and DATEADD(dd,d.number,'2012-10-01')=s.date
) t
------解决方案--------------------
- SQL code
--> 测试数据：#tbIF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tbGO CREATE TABLE #tb([id] INT,[hpid] INT,[storage] INT,[date] DATETIME)INSERT #tbSELECT 1,1,1000,'2012-10-10' UNION ALLSELECT 2,1,2000,'2012-10-12' UNION ALLSELECT 3,1,5000,'2012-10-13' UNION ALLSELECT 4,1,5000,'2012-10-14' UNION ALLSELECT 5,1,4500,'2012-10-18' UNION ALLSELECT 6,1,5340,'2012-10-22' UNION ALLSELECT 7,1,684,'2012-11-28' UNION ALLSELECT 8,1,10000,'2012-12-12'--------------开始查询--------------------------DECLARE @begindate DATETIME,@endtime DATETIMESET @begindate='2012-10-01'SET @endtime='2012-11-30'SELECT [id]=ISNULL((SELECT MAX([id]) FROM #tb WHERE [date]<=b.[date] ),1),[hpid]=ISNULL((SELECT MAX([hpid]) FROM #tb WHERE [date]<=b.[date] ),1),[storage]=ISNULL((SELECT MAX([storage]) FROM #tb WHERE [date]<=b.[date]),0),[date]FROM ( SELECT DATEADD(dd,number,@begindate) AS [date] ,[id],[hpid],[storage] FROM master..spt_values LEFT JOIN #tb b ON DATEADD(dd,number,@begindate)=b.[date] WHERE type='p' AND number BETWEEN 0 AND DATEDIFF(dd,@begindate,@endtime)) b----------------结果----------------------------/* id hpid storage date1 1 0 2012-10-01 00:00:00.0001 1 0 2012-10-02 00:00:00.0001 1 0 2012-10-03 00:00:00.0001 1 0 2012-10-04 00:00:00.0001 1 0 2012-10-05 00:00:00.0001 1 0 2012-10-06 00:00:00.0001 1 0 2012-10-07 00:00:00.0001 1 0 2012-10-08 00:00:00.0001 1 0 2012-10-09 00:00:00.0001 1 1000 2012-10-10 00:00:00.0001 1 1000 2012-10-11 00:00:00.0002 1 2000 2012-10-12 00:00:00.0003 1 5000 2012-10-13 00:00:00.0004 1 5000 2012-10-14 00:00:00.0004 1 5000 2012-10-15 00:00:00.0004 1 5000 2012-10-16 00:00:00.0004 1 5000 2012-10-17 00:00:00.0005 1 5000 2012-10-18 00:00:00.0005 1 5000 2012-10-19 00:00:00.0005 1 5000 2012-10-20 00:00:00.0005 1 5000 2012-10-21 00:00:00.0006 1 5340 2012-10-22 00:00:00.0006 1 5340 2012-10-23 00:00:00.0006 1 5340 2012-10-24 00:00:00.0006 1 5340 2012-10-25 00:00:00.0006 1 5340 2012-10-26 00:00:00.0006 1 5340 2012-10-27 00:00:00.0006 1 5340 2012-10-28 00:00:00.0006 1 5340 2012-10-29 00:00:00.0006 1 5340 2012-10-30 00:00:00.0006 1 5340 2012-10-31 00:00:00.0006 1 5340 2012-11-01 00:00:00.0006 1 5340 2012-11-02 00:00:00.0006 1 5340 2012-11-03 00:00:00.0006 1 5340 2012-11-04 00:00:00.0006 1 5340 2012-11-05 00:00:00.0006 1 5340 2012-11-06 00:00:00.0006 1 5340 2012-11-07 00:00:00.0006 1 5340 2012-11-08 00:00:00.0006 1 5340 2012-11-09 00:00:00.0006 1 5340 2012-11-10 00:00:00.0006 1 5340 2012-11-11 00:00:00.0006 1 5340 2012-11-12 00:00:00.0006 1 5340 2012-11-13 00:00:00.0006 1 5340 2012-11-14 00:00:00.0006 1 5340 2012-11-15 00:00:00.0006 1 5340 2012-11-16 00:00:00.0006 1 5340 2012-11-17 00:00:00.0006 1 5340 2012-11-18 00:00:00.0006 1 5340 2012-11-19 00:00:00.0006 1 5340 2012-11-20 00:00:00.0006 1 5340 2012-11-21 00:00:00.0006 1 5340 2012-11-22 00:00:00.0006 1 5340 2012-11-23 00:00:00.0006 1 5340 2012-11-24 00:00:00.0006 1 5340 2012-11-25 00:00:00.0006 1 5340 2012-11-26 00:00:00.0006 1 5340 2012-11-27 00:00:00.0007 1 5340 2012-11-28 00:00:00.0007 1 5340 2012-11-29 00:00:00.0007 1 5340 2012-11-30 00:00:00.000*/