我从一个表中查询一定时间数据时,是按天分组(group by )的.
但是,如果有几天没有数据,则不返回日期.
select indate,count(indate) from surveyDate
where indate > '2003-7-13' and indate<'2003-7-18'
group by indate如查询2003-7-13 到 2003-7-17 结果为:
2003-7-13 9
2003-7-15 23
2003-7-17 28
其中14,16号没数据,我要返回以下这样的结果
2003-7-13 9
2003-7-14 0
2003-7-15 23
2003-7-16 0
2003-7-17 28
该怎么写呢?
但是,如果有几天没有数据,则不返回日期.
select indate,count(indate) from surveyDate
where indate > '2003-7-13' and indate<'2003-7-18'
group by indate如查询2003-7-13 到 2003-7-17 结果为:
2003-7-13 9
2003-7-15 23
2003-7-17 28
其中14,16号没数据,我要返回以下这样的结果
2003-7-13 9
2003-7-14 0
2003-7-15 23
2003-7-16 0
2003-7-17 28
该怎么写呢?
select @a='2003-7-13',@b='2003-7-17'select * from (select dateadd(day,t5.c,@a) indate from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5 where dateadd(day,t5.c,@a)<=@b) t6
left join (select indate,count(indate) num from surveyDate where indate > @a and indate<@b group by indate) t7
on t6.indate=t7.indate
declare @date smalldatetime
set @date='2003-7-13'
while @date<'2003-7-18'
begin
insert #aa
values(@date,0 )set @date=dateadd(day,1,@date)
endinsert #aa
select indate,count(indate) from surveyDate
where indate> '2003-7-13' and indate<'2003-7-18'
group by indateselect indate,max(counts) from #aa group by indate
drop table #aa