如何实现没有的时间段中使用0来填充？？
if object_id('[A]') is not null drop table [A]
go
create table [A]([日期] datetime,[金额] int)
insert [A]
select * from [A]
select '2014-05-01',100 union all
select '2014-05-02',200 union all
select '2014-05-05',300 union all
select '2014-05-06',200
declare @stdate smalldatetime,
@eddate smalldatetime
select @stdate=min([日期]),
@eddate=max([日期]) from [A]
;
with sel as
(
select @stdate as [date],1 as val
union all
select dateadd(day,val,@stdate),val+1 as val from sel where val<=datediff(day,@stdate,@eddate)
)
select sel.[date] as [日期],sum(isnull(A.[金额],0)) as [金额] from sel
left join [A] on sel.[date]=[A].[日期] group by sel.[date]
解释一下：with这一步是关键，通过它使得“select dateadd(day,val,@stdate),val+1 as val from sel where val<=datediff(day,@stdate,@eddate)”一直在查询时间差
然后通过外链接关联