按日期数量统计的问题：

[K1933]

SELECT nday,count(*) FROM lm GROUP BY nday
出来：
2004/05/06 10
2004/05/09 3

而我所想要的结果是如果没有记录的日期也出来，面统计数为0，如：
2004/05/06 10
2004/05/07 0
2004/05/08 0
2004/05/09 3

怎么办，注：日期是变动的。

[yesterday2000]

select
isnull (a.nday,b.nday) as nday,
isnull (b.num,0 ) as num

(
select nday from lm group by nday) a full join (
SELECT nday,count(*) as num FROM lm GROUP BY nday
) b
on a.nday=b.nday

[hisi]

--创建一个表
create #tmp
(
[time] datetime
)

--输入连续的日期
declare @i int
set @i = 100
while @i > 0
begin
insert into #tmp select dateadd(day,@i,'2004/05/06')
end

--输出
select [time],count(id) from ( select [time],id from 表 union all select [time],null as ID from #tmp ) t

drop table #tmp

哈哈，方法比较笨~~。

[zjcxc]

declare @dt datetime,@i int
select @dt=min(nday),@i=datediff(day,max(nday),min(nday))+1
from lm
if @@rowcount=0 return

set rowcount @i
select id=identity(int,0,1),a=0 into #t from syscolumns a,syscolumns b
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t select 0 from syscolumns a,syscolumns b
set @i=@i-@@rowcount
end
set rowcount 0

select nday=dateadd(day,a.id,@dt),[count]=isnull(b.[count],0)
from #t a left join(
SELECT nday,[count]=count(*) FROM lm GROUP BY nday
)b on a.id=datediff(day,@dt,b.nday)

drop table #t

[zsforever]

因为你的表中没有其中几天的记录

[hisi]

--创建一个表
create #tmp
(
[time] datetime
)

--输入连续的日期
declare @i int
set @i = 100
while @i > 0
begin
insert into #tmp select dateadd(day,@i,'2004/05/06')
set @i = @i - 1
end

--输出
select [time],count(id) from ( select [time],id from 表 union all select [time],null as ID from #tmp ) t group by [time]

drop table #tmp

哈哈，方法比较笨~~。

[chinaandys]

用 left join