显示两个日期之间的所有日期数据；如果特定日期不存在行，则在所有列中显示零 [英] SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns

问题描述

当缺少任何日期数据时，我想显示两个日期之间的所有日期，然后它应该在 val 列中显示为零.

I want to show all dates between two dates when there is any date data missing then its should show zero in val column .

declare @temp table (
id int identity(1,1) not null,
CDate smalldatetime ,
val int
)

插入数据检查声明

insert into @temp select '10/2/2012',1
insert into @temp select '10/3/2012',1
insert into @temp select '10/5/2012',1
insert into @temp select '10/7/2012',2
insert into @temp select '10/9/2012',2
insert into @temp select '10/10/2012',2
insert into @temp select '10/13/2012',2
insert into @temp select '10/15/2012',2

检索一个月的第一天和今天之间的记录

Retrieve records between first day of month and today

select * from @temp where CDate between '10/01/2012' AND '10/15/2012'

当我运行这个查询时，它会显示这两个日期之间的所有数据，但我还想用 val=0

As i run this query its show me all data between these two dates but i want to also include missing dates with val=0

使用示例数据的 SQL FIDDLE

推荐答案

;with d(date) as (
  select cast('10/01/2012' as datetime)
  union all
  select date+1
  from d
  where date < '10/15/2012'
  )
select t.ID, d.date CDate, isnull(t.val, 0) val
from d
left join temp t
       on t.CDate = d.date
order by d.date
OPTION (MAXRECURSION 0) -- use this if your dates are >99 days apart

您需要补上日期，所以我在这里使用了递归公用表表达式.SQL 小提琴

You need to make up the dates, so I've use a recursive common table expression here. SQL Fiddle

最大递归数

指定此查询允许的最大递归数.number 是非负数0 到 32767 之间的整数.指定 0 时，不应用任何限制.如果这个选项是未指定，服务器的默认限制为 100.

Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

当查询期间达到 MAXRECURSION 限制的指定或默认数量时执行，查询结束并返回错误.

When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

这篇关于显示两个日期之间的所有日期数据；如果特定日期不存在行，则在所有列中显示零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！