显示两个日期之间的所有日期数据；如果特定日期不存在行，则所有列中显示零

Question

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

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

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

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

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

带有示例数据的 SQL FIDDLE

Answer 1

;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 Fiddle

最大循环数

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

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

Answer 2

只要从日期到结束日期之间的天数少于 2047 天，此操作就有效

declare @from smalldatetime = '10/01/2012'
declare @to smalldatetime = '10/15/2012'

select t.id, dateadd(day, number,@from), isnull(val, 0) val from @temp t
right join master..spt_values s
on dateadd(d, s.number, @from) = t.CDate
where
datediff(day, @from, @to ) > s.number
and s.type = 'P'

Answer 3

我认为最好的方法是使用日期创建自己的表（您也可以使用 master.dbo.spt_values，但我个人不喜欢该解决方案）

declare @Temp_Dates table (CDate datetime)
declare @Date datetime
select @Date = (select min(CDate) from temp)

while @Date <= (select max(CDate) from temp)
begin
    insert into @Temp_Dates (CDate)
    select @Date

    select @Date = dateadd(dd, 1, @Date)
end

select D.CDate, isnull(T.id, 0) as id
from @Temp_Dates as D
    left outer join temp as T on T.CDate = D.CDate

您也可以使用 CTE 的递归解决方案

Answer 4

DECLARE @min DATETIME, 
        @max DATETIME, 
        @val INT 

SELECT @min = Min(CDATE), 
       @max = Max(CDATE) 
FROM   TEMP 

DECLARE @temp TABLE 
  ( 
     CDATE SMALLDATETIME, 
     VAL   INT 
  ) 

WHILE @min < @max 
  BEGIN 
      SELECT @val = VAL 
      FROM   TEMP 
      WHERE  CDATE = @min 

      INSERT @temp 
      VALUES (@min, 
              @val) 

      SET @min = Dateadd(D, 1, @min) 
      SET @val = 0 
  END 

SELECT * 
FROM   @temp 

Answer 5

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

DECLARE @startDate DATE= '10/01/2012'
DECLARE @endDate DATE= '10/15/2012'

SELECT t.Id, X.[Date],Val = COALESCE(t.val,0)
FROM 
    (SELECT [Date] = DATEADD(Day,Number,@startDate)  
    FROM  master..spt_values  
    WHERE Type='P' 
    AND DATEADD(day,Number,@startDate) <= @endDate)X
LEFT JOIN  @temp t 
ON X.[Date] = t.CDate

Answer 6

使用具有最小值和最大值的递归 cte

declare @T table (id int identity(1,1) primary key, dt date not null, val int not null);
insert into @T (dt, val) values 
       ('10/2/2012',1)
     , ('10/3/2012',1)
     , ('10/5/2012',1)
     , ('10/7/2012',2)
     , ('10/9/2012',2)
     , ('10/10/2012',2)
     , ('10/13/2012',2)
     , ('10/15/2012',2);
--select * from @T;
with cte as 
( select min(dt) as dt, max(dt) as mx 
  from @T 
  union all 
  select dateadd(dd, 1, dt), mx 
  from CTE 
  where dt < mx
)
select c.dt, isnull(t.val, 0) as val 
from cte c
left join @T t
on c.dt = t.dt
order by c.dt
option (maxrecursion 0);

dt         val
---------- -----------
2012-10-02 1
2012-10-03 1
2012-10-04 0
2012-10-05 1
2012-10-06 0
2012-10-07 2
2012-10-08 0
2012-10-09 2
2012-10-10 2
2012-10-11 0
2012-10-12 0
2012-10-13 2
2012-10-14 0
2012-10-15 2