SQL Server:即使某些天不存在数据，如何选择日期范围内的所有天 [英] SQL Server: How to select all days in a date range even if no data exists for some days

问题描述

我有一个应用需要显示过去 30 天活动的条形图.即使当天没有活动，图表也需要显示所有天数.

I have an app that needs to show a bar graph for activity over the last 30 days. The graph needs to show all days even if there is no activity for the day.

例如:

DATE       COUNT
==================
1/1/2011   5 
1/2/2011   3 
1/3/2011   0
1/4/2011   4
1/5/2011   0
etc....

我可以在查询后进行后期处理，以确定缺少哪些日期并添加它们，但想知道是否有更简单的方法在 SQL Server 中执行此操作.非常感谢

I could do post processing after the query to figure out what dates are missing and add them but was wondering if there is an easier way to do it in SQL Server. Thanks much

推荐答案

您可以使用递归 CTE 来构建您的 30 天列表，然后将其加入到您的数据中

You can use a recursive CTE to build your list of 30 days, then join that to your data

--test
select cast('05 jan 2011' as datetime) as DT, 1 as val into #t
union all select CAST('05 jan 2011' as datetime), 1 
union all select CAST('29 jan 2011' as datetime), 1 

declare @start datetime = '01 jan 2011'
declare @end   datetime = dateadd(day, 29, @start)

;with amonth(day) as
(
    select @start as day
        union all
    select day + 1
        from amonth
        where day < @end
)
select amonth.day, count(val)
    from amonth 
    left join #t on #t.DT = amonth.day
group by amonth.day


>>

2011-01-04 00:00:00.000 0
2011-01-05 00:00:00.000 2
2011-01-06 00:00:00.000 0
2011-01-07 00:00:00.000 0
2011-01-08 00:00:00.000 0
2011-01-09 00:00:00.000 0
...

这篇关于SQL Server:即使某些天不存在数据，如何选择日期范围内的所有天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！