给你个思路：把表按时间排序输出：
循环输出时，看表中的时间与上一次的时间（用一个变量DA来代替）
如果正好，那么输出，如果差的大于1了，用个FOR循环把DA一直也1,直到与时间差1为止。
time=#2009-1-1#
set rs=conn.execute("select [cont] from [表] order by nowtime asc"）
do while not rs.eof
if rs("nowtime")=time then
'输出数据库中内容
else
for i=0 to （两个时间的时间差）
‘输出为空时补上行
time时间加上一天
next
end if
rs.movenext
loop
要实现你的功能关键是用好DATEPART函数和LEFT JOIN
DATEPART(dy, GATEDATE())这样可以得到该日期在一年中是第几天，然后就可以把日期当成一个int类型数列
下面的例子实现了一年内的，如果要跨年，你需要再加一个年份的判断，按照下面的例子不难搞
DECLARE @tb Table(dy int)
DECLARE @firstYearDay Varchar(30)
DECLARE @dateFrom Varchar(30)
DECLARE @dateTo Varchar(30)
DECLARE @dy int
SET @dateFrom = '2009-1-1'
SET @dateTo = '2009-1-8'
SET @firstYearDay = DATEADD(dd, -DATEPART(dy, @dateFrom) + 1, @dateFrom)
IF YEAR(@dateFrom) = YEAR(@dateTo)
BEGIN
SET @dy = DATEPART(dy, @dateFrom)
WHILE @dy <= DATEPART(dy, @dateTo)
BEGIN
INSERT INTO @tb VALUES(@dy)
SET @dy = @dy + 1
END
--执行查询，把temptb换成你所要查询的表
SELECT DATEADD(dd, dy - 1, @firstYearDay) AS 日期, temptb.*
FROM @tb t
LEFT JOIN temptb ON DATEPART(dy, nowtime) = t.dy
END
SQL2000临时表，05以上用CTE
如：
--> -->
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([nowtime] Datetime,[title] nvarchar(3),[cont] decimal(18,2))
Insert #T
select '2009-1-1',N'标题1',100.00 union all
select '2009-1-2',N'标题1',200.00 union all
select '2009-1-5',N'标题1',400.00 union all
select '2009-1-8',N'标题1',150.00
Go
declare @dt1 datetime,@dt2 datetime
select @dt1=min([nowtime]),@dt2=max([nowtime]) from #T
;with dt
as
(select @dt1 as Date
union all
select Date+1 as date from dt where Date<@dt2)
,dt2
as
(select * from dt,(select distinct [title] from #T)t)
select a.Date,
a.[title],
sum(isnull([cont],0)) as [cont]
from dt2 a
left join #T b on a.date=b.[nowtime] and a.[title]=b.[title]
group by a.date,a.[title]
Date title cont
----------------------- ----- ---------------------------------------
2009-01-01 00:00:00.000 标题1 100.00
2009-01-02 00:00:00.000 标题1 200.00
2009-01-03 00:00:00.000 标题1 0.00
2009-01-04 00:00:00.000 标题1 0.00
2009-01-05 00:00:00.000 标题1 400.00
2009-01-06 00:00:00.000 标题1 0.00
2009-01-07 00:00:00.000 标题1 0.00
2009-01-08 00:00:00.000 标题1 150.00
(8 个资料列受到影响)
<%
date1="2009-1-1"
date2="2009-1-8"
set rs=conn.execute("select nowtime,[cont] from [表] where nowtime between '"&date1&"' and '"&date2&"'")
for i=0 to DateDiff("d", date1, date2)
if rs("nowtime")=dateAdd("d",i,date1) and not rs.eof then
cont=rs("cont")
rs.movenext
else
cont=0
end if
next
%>
我自己的感觉应该是这样！
1.定义一个游标
DECLARE INQ_CUR CURSOR
FOR
SELECT NOWTIME,TITLE,CONT
FROM 表
2.依次FETCH
判断NOWTIME是否为上一个NOWTIME+1 day
是则正常显示
否则显示========