sql 时间段内没有的数据等于0
时间：2021-07-01 10:21:17
帮助过：3人阅读
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)”一直在查询时间差
然后通过外链接关联
sql 时间段内没有的数据等于0
标签：
