关于sql 2000的日期问题，较复杂

Question

我有一张表，字段为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语句，望大家指教！

Answer 1

我使用的数据库是sql server 2000

Answer 2

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

Answer 3

--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO 
CREATE TABLE #tb([id] INT,[hpid] INT,[storage] INT,[date] DATETIME)
INSERT #tb
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'
--------------开始查询--------------------------DECLARE @begindate DATETIME,@endtime DATETIME
SET @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 date
1 1 0 2012-10-01 00:00:00.000
1 1 0 2012-10-02 00:00:00.000
1 1 0 2012-10-03 00:00:00.000
1 1 0 2012-10-04 00:00:00.000
1 1 0 2012-10-05 00:00:00.000
1 1 0 2012-10-06 00:00:00.000
1 1 0 2012-10-07 00:00:00.000
1 1 0 2012-10-08 00:00:00.000
1 1 0 2012-10-09 00:00:00.000
1 1 1000 2012-10-10 00:00:00.000
1 1 1000 2012-10-11 00:00:00.000
2 1 2000 2012-10-12 00:00:00.000
3 1 5000 2012-10-13 00:00:00.000
4 1 5000 2012-10-14 00:00:00.000
4 1 5000 2012-10-15 00:00:00.000
4 1 5000 2012-10-16 00:00:00.000
4 1 5000 2012-10-17 00:00:00.000
5 1 5000 2012-10-18 00:00:00.000
5 1 5000 2012-10-19 00:00:00.000
5 1 5000 2012-10-20 00:00:00.000
5 1 5000 2012-10-21 00:00:00.000
6 1 5340 2012-10-22 00:00:00.000
6 1 5340 2012-10-23 00:00:00.000
6 1 5340 2012-10-24 00:00:00.000
6 1 5340 2012-10-25 00:00:00.000
6 1 5340 2012-10-26 00:00:00.000
6 1 5340 2012-10-27 00:00:00.000
6 1 5340 2012-10-28 00:00:00.000
6 1 5340 2012-10-29 00:00:00.000
6 1 5340 2012-10-30 00:00:00.000
6 1 5340 2012-10-31 00:00:00.000
6 1 5340 2012-11-01 00:00:00.000
6 1 5340 2012-11-02 00:00:00.000
6 1 5340 2012-11-03 00:00:00.000
6 1 5340 2012-11-04 00:00:00.000
6 1 5340 2012-11-05 00:00:00.000
6 1 5340 2012-11-06 00:00:00.000
6 1 5340 2012-11-07 00:00:00.000
6 1 5340 2012-11-08 00:00:00.000
6 1 5340 2012-11-09 00:00:00.000
6 1 5340 2012-11-10 00:00:00.000
6 1 5340 2012-11-11 00:00:00.000
6 1 5340 2012-11-12 00:00:00.000
6 1 5340 2012-11-13 00:00:00.000
6 1 5340 2012-11-14 00:00:00.000
6 1 5340 2012-11-15 00:00:00.000
6 1 5340 2012-11-16 00:00:00.000
6 1 5340 2012-11-17 00:00:00.000
6 1 5340 2012-11-18 00:00:00.000
6 1 5340 2012-11-19 00:00:00.000
6 1 5340 2012-11-20 00:00:00.000
6 1 5340 2012-11-21 00:00:00.000
6 1 5340 2012-11-22 00:00:00.000
6 1 5340 2012-11-23 00:00:00.000
6 1 5340 2012-11-24 00:00:00.000
6 1 5340 2012-11-25 00:00:00.000
6 1 5340 2012-11-26 00:00:00.000
6 1 5340 2012-11-27 00:00:00.000
7 1 5340 2012-11-28 00:00:00.000
7 1 5340 2012-11-29 00:00:00.000
7 1 5340 2012-11-30 00:00:00.000
*/

Answer 4

谢谢给以答复的人 ，CSDN有你更精彩！

Answer 5

我真想说，hpid 不一样怎么办...虽然我的代码没格式...也不要忽视。

Answer 6

做了修改。DECLARE @begindate DATETIME,@endtime DATETIME
SET @begindate='2012-10-01'
SET @endtime='2012-11-30'SELECT [id]=ISNULL((SELECT MAX([id]) FROM #tb WHERE  [date] BETWEEN @begindate AND @endtime AND [date]<=b.[date] ),1),
[hpid]=ISNULL([hpid],1),
[storage]=ISNULL((SELECT TOP 1 [storage] FROM #tb WHERE  [date] BETWEEN @begindate AND @endtime AND [date]<=b.[date] ORDER BY [date] DESC),0),
[date]
FROM  (
    SELECT DATEADD(dd,number,@begindate) AS [date] ,[id],[hpid],[storage]
    FROM master..spt_values 
    LEFT JOIN
   (SELECT * FROM #tb WHERE [date] BETWEEN @begindate AND @endtime) t
    ON DATEADD(dd,number,@begindate)=t.[date]
    WHERE type='p' AND number BETWEEN 0 AND DATEDIFF(dd,@begindate,@endtime)
   
) b

Answer 7

楼主结贴好块啊，楼上的代码有问题，当时间跨度很大，用master.dbo.spt_values会有问题的。

Answer 8

贴一个我写的create table #tb(id int,hpid int,storage int,date varchar(30))
insert into #tb
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'
insert into #tb
select 1,2,1000,'2012-10-10' union all
select 2,2,2000,'2012-10-12' union all
select 3,2,5000,'2012-10-13' union all
select 4,2,5000,'2012-10-14' union all
select 5,2,4500,'2012-10-18' union all
select 6,3,5340,'2012-10-22' union all
select 7,3,684,'2012-11-28' union all
select 8,3,10000,'2012-12-12'
--1.
select *
from
(
select *,(select count(*) from #tb b where a.hpid=b.hpid and b.date>a.date) as rownum
from #tb a
where a.date between '2012-10-10' and '2012-12-10'
) as tmp
where rownum=1--2
select *,(select top 1 storage 
from #tb b 
where a.hpid=b.hpid and cast(b.date as datetime)<=a.month_day order by b.date desc)
from
(
select distinct hpid,dateadd(day,b.number,cast(left(date,8)+'01' as datetime)) as month_day
from #tb a inner join master.dbo.spt_values b on b.type='p' and b.number between 0 and datediff(day,cast(left(date,8)+'01' as datetime),dateadd(month,1,cast(left(date,8)+'01' as datetime)))
where a.date between '1899-10-01' and '2012-11-30'
) as a
where a.month_day between '1899-10-01' and '2012-11-30'--整月统计就注释，按天统计就取消注释
order by a.hpid,a.month_day

Answer 9

--结果
/**
1 2012-10-01 00:00:00.000 NULL
1 2012-10-02 00:00:00.000 NULL
1 2012-10-03 00:00:00.000 NULL
1 2012-10-04 00:00:00.000 NULL
1 2012-10-05 00:00:00.000 NULL
1 2012-10-06 00:00:00.000 NULL
1 2012-10-07 00:00:00.000 NULL
1 2012-10-08 00:00:00.000 NULL
1 2012-10-09 00:00:00.000 NULL
1 2012-10-10 00:00:00.000 1000
1 2012-10-11 00:00:00.000 1000
1 2012-10-12 00:00:00.000 2000
1 2012-10-13 00:00:00.000 5000
1 2012-10-14 00:00:00.000 5000
1 2012-10-15 00:00:00.000 5000
1 2012-10-16 00:00:00.000 5000
1 2012-10-17 00:00:00.000 5000
1 2012-10-18 00:00:00.000 4500
1 2012-10-19 00:00:00.000 4500
1 2012-10-20 00:00:00.000 4500
1 2012-10-21 00:00:00.000 4500
1 2012-10-22 00:00:00.000 5340
1 2012-10-23 00:00:00.000 5340
1 2012-10-24 00:00:00.000 5340
1 2012-10-25 00:00:00.000 5340
1 2012-10-26 00:00:00.000 5340
1 2012-10-27 00:00:00.000 5340
1 2012-10-28 00:00:00.000 5340
1 2012-10-29 00:00:00.000 5340
1 2012-10-30 00:00:00.000 5340
1 2012-10-31 00:00:00.000 5340
1 2012-11-01 00:00:00.000 5340
1 2012-11-02 00:00:00.000 5340
1 2012-11-03 00:00:00.000 5340
1 2012-11-04 00:00:00.000 5340
1 2012-11-05 00:00:00.000 5340
1 2012-11-06 00:00:00.000 5340
1 2012-11-07 00:00:00.000 5340
1 2012-11-08 00:00:00.000 5340
1 2012-11-09 00:00:00.000 5340
1 2012-11-10 00:00:00.000 5340
1 2012-11-11 00:00:00.000 5340
1 2012-11-12 00:00:00.000 5340
1 2012-11-13 00:00:00.000 5340
1 2012-11-14 00:00:00.000 5340
1 2012-11-15 00:00:00.000 5340
1 2012-11-16 00:00:00.000 5340
1 2012-11-17 00:00:00.000 5340
1 2012-11-18 00:00:00.000 5340
1 2012-11-19 00:00:00.000 5340
1 2012-11-20 00:00:00.000 5340
1 2012-11-21 00:00:00.000 5340
1 2012-11-22 00:00:00.000 5340
1 2012-11-23 00:00:00.000 5340
1 2012-11-24 00:00:00.000 5340
1 2012-11-25 00:00:00.000 5340
1 2012-11-26 00:00:00.000 5340
1 2012-11-27 00:00:00.000 5340
1 2012-11-28 00:00:00.000 684
1 2012-11-29 00:00:00.000 684
1 2012-11-30 00:00:00.000 684
2 2012-10-01 00:00:00.000 NULL
2 2012-10-02 00:00:00.000 NULL
2 2012-10-03 00:00:00.000 NULL
2 2012-10-04 00:00:00.000 NULL
2 2012-10-05 00:00:00.000 NULL
2 2012-10-06 00:00:00.000 NULL
2 2012-10-07 00:00:00.000 NULL
2 2012-10-08 00:00:00.000 NULL
2 2012-10-09 00:00:00.000 NULL
2 2012-10-10 00:00:00.000 1000
2 2012-10-11 00:00:00.000 1000
2 2012-10-12 00:00:00.000 2000
2 2012-10-13 00:00:00.000 5000
2 2012-10-14 00:00:00.000 5000
2 2012-10-15 00:00:00.000 5000
2 2012-10-16 00:00:00.000 5000
2 2012-10-17 00:00:00.000 5000
2 2012-10-18 00:00:00.000 4500
2 2012-10-19 00:00:00.000 4500
2 2012-10-20 00:00:00.000 4500
2 2012-10-21 00:00:00.000 4500
2 2012-10-22 00:00:00.000 4500
2 2012-10-23 00:00:00.000 4500
2 2012-10-24 00:00:00.000 4500
2 2012-10-25 00:00:00.000 4500
2 2012-10-26 00:00:00.000 4500
2 2012-10-27 00:00:00.000 4500
2 2012-10-28 00:00:00.000 4500
2 2012-10-29 00:00:00.000 4500
2 2012-10-30 00:00:00.000 4500
2 2012-10-31 00:00:00.000 4500
2 2012-11-01 00:00:00.000 4500
3 2012-10-01 00:00:00.000 NULL
3 2012-10-02 00:00:00.000 NULL
3 2012-10-03 00:00:00.000 NULL
3 2012-10-04 00:00:00.000 NULL
3 2012-10-05 00:00:00.000 NULL
3 2012-10-06 00:00:00.000 NULL
3 2012-10-07 00:00:00.000 NULL
3 2012-10-08 00:00:00.000 NULL
3 2012-10-09 00:00:00.000 NULL
3 2012-10-10 00:00:00.000 NULL
3 2012-10-11 00:00:00.000 NULL
3 2012-10-12 00:00:00.000 NULL
3 2012-10-13 00:00:00.000 NULL
3 2012-10-14 00:00:00.000 NULL
3 2012-10-15 00:00:00.000 NULL
3 2012-10-16 00:00:00.000 NULL
3 2012-10-17 00:00:00.000 NULL
3 2012-10-18 00:00:00.000 NULL
3 2012-10-19 00:00:00.000 NULL
3 2012-10-20 00:00:00.000 NULL
3 2012-10-21 00:00:00.000 NULL
3 2012-10-22 00:00:00.000 5340
3 2012-10-23 00:00:00.000 5340
3 2012-10-24 00:00:00.000 5340
3 2012-10-25 00:00:00.000 5340
3 2012-10-26 00:00:00.000 5340
3 2012-10-27 00:00:00.000 5340
3 2012-10-28 00:00:00.000 5340
3 2012-10-29 00:00:00.000 5340
3 2012-10-30 00:00:00.000 5340
3 2012-10-31 00:00:00.000 5340
3 2012-11-01 00:00:00.000 5340
3 2012-11-02 00:00:00.000 5340
3 2012-11-03 00:00:00.000 5340
3 2012-11-04 00:00:00.000 5340
3 2012-11-05 00:00:00.000 5340
3 2012-11-06 00:00:00.000 5340
3 2012-11-07 00:00:00.000 5340
3 2012-11-08 00:00:00.000 5340
3 2012-11-09 00:00:00.000 5340
3 2012-11-10 00:00:00.000 5340
3 2012-11-11 00:00:00.000 5340
3 2012-11-12 00:00:00.000 5340
3 2012-11-13 00:00:00.000 5340
3 2012-11-14 00:00:00.000 5340
3 2012-11-15 00:00:00.000 5340
3 2012-11-16 00:00:00.000 5340
3 2012-11-17 00:00:00.000 5340
3 2012-11-18 00:00:00.000 5340
3 2012-11-19 00:00:00.000 5340
3 2012-11-20 00:00:00.000 5340
3 2012-11-21 00:00:00.000 5340
3 2012-11-22 00:00:00.000 5340
3 2012-11-23 00:00:00.000 5340
3 2012-11-24 00:00:00.000 5340
3 2012-11-25 00:00:00.000 5340
3 2012-11-26 00:00:00.000 5340
3 2012-11-27 00:00:00.000 5340
3 2012-11-28 00:00:00.000 684
3 2012-11-29 00:00:00.000 684
3 2012-11-30 00:00:00.000 684
**/