sql-server - 如何从具有从/到日期列的单个记录中获取派生 'N' 日期行？

Question

标题听起来很困惑，但让我解释一下:

我有一个表，其中两列提供日期范围，一列提供值。我需要查询该表并“详细说明”这样的数据

是否可以只使用 TSQL？

附加信息

有问题的表大约有 2-3 百万条记录(并且还在增长)

Answer 1

假设日期范围相当窄，另一种方法是使用递归 CTE 创建范围内所有日期的列表，然后加入插值:

WITH LastDay AS
(
  SELECT MAX(Date_To) AS MaxDate
  FROM MyTable
),
Days AS
(
  SELECT MIN(Date_From) AS TheDate
  FROM MyTable

  UNION ALL

  SELECT DATEADD(d, 1, TheDate) AS TheDate
  FROM Days CROSS JOIN LastDay
  WHERE TheDate <= LastDay.MaxDate
)
SELECT mt.Item_ID, mt.Cost_Of_Item, d.TheDate
FROM MyTable mt
INNER JOIN Days d
ON d.TheDate BETWEEN mt.Date_From AND mt.Date_To;

我还假定日期起始日期和日期代表一个包含范围(即包括两个边)——在日期上使用包含范围的 BETWEEN 是不常见的。

SqlFiddle here

编辑

Sql Server 中递归 CTE 的默认 MAXRECURSION 为 100，这会将查询中的日期范围限制为 100 天。您可以将其调整为 maximum of 32767 .

此外，如果您只过滤大表中的较小日期范围，您可以调整 CTE 以限制该范围内的天数:

WITH DateRange AS
(
  SELECT CAST('2014-01-01' AS DATE) AS MinDate, 
         CAST('2014-02-16' AS DATE) AS MaxDate
),
Days AS
(
  SELECT MinDate AS TheDate
  FROM DateRange

  UNION ALL

  SELECT DATEADD(d, 1, TheDate) AS TheDate
  FROM Days CROSS APPLY DateRange
  WHERE TheDate <= DateRange.MaxDate
)
SELECT mt.Item_ID, mt.Cost_Of_Item, d.TheDate
FROM MyTable mt
INNER JOIN Days d
ON d.TheDate BETWEEN mt.Date_From AND mt.Date_To
OPTION (MAXRECURSION 0);

Update Fiddle