对于两个日期之间的每一天，在开始/结束列中添加一行具有相同信息但仅包含当天的信息

我有一个表，类型为 varchar 、datetime、datetime：

NAME | START | END
Bob  | 10/30 | 11/2

我可以查找什么是 SQL 查询以了解如何使该表成为？

NAME | START | END
Bob  | 10/30 | 10/30
Bob  | 10/31 | 10/31
Bob  | 11/01 | 11/01
Bob  | 11/02 | 11/02

这只运行一次，而且在一个非常小的数据集上。不需要优化。

也许你需要一个Recursive CTE。

CREATE TABLE #dates(NAME  VARCHAR(50),START DATETIME,[END] DATETIME)
INSERT INTO #dates
VALUES      ('Bob','2014-10-30','2014-11-02')
DECLARE @maxdate DATETIME = (SELECT Max([end]) FROM   #dates);
WITH cte
     AS (SELECT NAME,
                START,
                [END]
         FROM   #dates
         UNION ALL
         SELECT NAME,
                Dateadd(day, 1, start),
                Dateadd(day, 1, start)
         FROM   cte
         WHERE  start < @maxdate)
SELECT *
FROM   cte

输出：

name    START       END
----    ----------  ----------
Bob     2014-10-30  2014-10-30
Bob     2014-10-31  2014-10-31
Bob     2014-11-01  2014-11-01
Bob     2014-11-02  2014-11-02

您可以使用递归 cte 执行此操作：

;with cte AS (SELECT Name,Start,[End]
              FROM YourTable
              UNION  ALL
              SELECT Name
                    ,DATEADD(day,1,Start)
                    ,[End]
              FROM cte
              WHERE Start < [End])
SELECT Name, Start, Start AS [End]
FROM cte

但是，我建议创建一个日历表并加入它：

SELECT a.Name,b.CalendarDate AS Start, b.CalendarDate AS [End]
FROM YourTable a
JOIN tlkp_Calendar b
  ON b.CalendarDate BETWEEN a.[Start] AND a.[End]

两个查询的演示：SQL Fiddle

我对这个问题/答案的问题是它仅适用于一条记录。我在这个答案帖子中找到了一个简单有效的解决方案 - SQL如何将具有日期范围的行转换为每个日期的许多行。

"RichardTheKiwi"的解决方案涉及基于整数表(列表(添加新的日期记录，并通过使用datediff函数评估日期范围来连接到源表。可以直接从 SQL Server 主数据库 (SELECT master..spt_values WHERE v.type='P'( 拉取整数列表。这

谷歌搜索词sql spt_values，有很多关于这个表的有趣的博客文章。例如。。。

生成不带循环的集合或序列 – 第 2 部分
使用系统表spt_values的技巧 – SQL Server

完整解决方案：

--NAME | START | END
--Bob  | 10/30 | 11/2
DECLARE @SampleData as table 
    (PersonName nvarchar(50), StartDate date, EndDate date)
INSERT INTO @SampleData
    (PersonName, StartDate, EndDate)
VALUES
    ('Bob', '2019-10-30', '2019-11-02')
    , ('Joe', '2019-10-30', '2019-11-05')
;
WITH 
cteSampleData_RecordAdded AS
-- NOTE: Range record converted to daily records for 'SampleData'
(
    SELECT 
        T1.PersonName
        , T1.StartDate
        , T1.EndDate
        , DATEADD(d,v.number,T1.StartDate) AS [NewRecordDate]
        , DATEDIFF(day, T1.StartDate, T1.EndDate)+1 AS [QDaysActive]
    FROM 
        @SampleData T1
        -- Adds a record for each date in the range
        JOIN MASTER..spt_values v 
            ON v.type='P'AND v.number BETWEEN 0 AND datediff(d, T1.StartDate, T1.EndDate)
)
select * from cteSampleData_RecordAdded

结果：

+------------+------------+-----------+---------------+-------------+
| PersonName | StartDate  | EndDate   | NewRecordDate | QDaysActive |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 10/30/2019    | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 10/31/2019    | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 11/1/2019     | 4           |
+------------+------------+-----------+---------------+-------------+
| Bob        | 10/30/2019 | 11/2/2019 | 11/2/2019     | 4           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 10/30/2019    | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 10/31/2019    | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/1/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/2/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/3/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/4/2019     | 7           |
+------------+------------+-----------+---------------+-------------+
| Joe        | 10/30/2019 | 11/5/2019 | 11/5/2019     | 7           |
+------------+------------+-----------+---------------+-------------+

;WITH dates
 AS (SELECT (SELECT MIN(start) from table) as date,
     UNION ALL
     SELECT
            Dateadd(day, 1, date),
     FROM   dates
     WHERE  date < (SELECT MAX(end) from table))
SELECT name, date as start, date as end
from table
RIGHT JOIN dates on date between start and end

Hart CO 的答案中提到的日历解决方案始终是我的首选解决方案，但我要补充一点，您可以使用递归 CTE 即时创建自己的"日历表">

这个答案与其他

答案略有不同，因为它表明您不需要递归初始表 - 这使得这是一个更"复制和粘贴"的解决方案

在大多数情况下，我仍然建议您在数据库中使用专用日历表

"日历表"CTE

在 SQL Server 中，您不必在查询中包含FROM，因此您可以在所需的任何日期之间快速启动"日历表"(几乎，下面提供了更多详细信息(：

WITH dates AS (
    SELECT CAST('2014-01-01' AS DATE) AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < '2015-01-01'
)
SELECT *
FROM dates
OPTION(MAXRECURSION 500)
;

这将创建一个表，其中包含 2014-01-01 和 2015-01-01(含(之间的每天的单列、date_axis 和一行

我已经硬编码了 2014-01-01 和 2015-01-01，但您可以使用它的变体来处理您需要的任何内容——例如在运行时传入值(如果查询是通过 BI 工具或脚本运行的(，或者通过相对当前日期构建(如果您只需要一个相对范围(：

WITH dates AS (
    SELECT DATEADD(DAY, -365, CAST(GETDATE() AS DATE)) AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < CAST(GETDATE() AS DATE)
)
SELECT *
FROM dates
OPTION(MAXRECURSION 500)
;

OPTION(MAXRECURSION 500)是 SQL Server 语法，用于设置可以在递归 CTE 中执行的迭代次数。由于每个循环对应于添加一天，因此如果您需要超过 500 天，使用 OPTION(MAXRECURSION 500) 会出错(顺便说一下，默认值为 100(

您可以设置的最大值是32767，不到 90 年 - 如果您需要比这更多的天数，您绝对应该考虑创建一个物理日历表。 😝

回答问题

为了回答这个问题，设置递归 CTE 边界的另一个选项是使用一些变量(在支持它们的数据库中(或任何其他支持的机制来拉取最小和最大边界：

DECLARE @MINDATE DATE = (SELECT MIN("start") FROM example);
DECLARE @MAXDATE DATE = (SELECT MAX("end")   FROM example);
WITH dates AS (
    SELECT @MINDATE AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < @MAXDATE
)
SELECT *
FROM dates
OPTION(MAXRECURSION 500)
;

请注意，Pரதீப் 是正确的，当他们说溢出的答案由于递归 CTE 的(子查询(中的聚合而不起作用时 - 至少，对于 SQL Server，这是不允许的，因此变量方法对于 OPs 数据库会更正确

最后一部分只是将原始表连接回"日历表"以获取输出：

DECLARE @MINDATE DATE = (SELECT MIN("start") FROM example);
DECLARE @MAXDATE DATE = (SELECT MAX("end")   FROM example);
WITH dates AS (
    SELECT @MINDATE AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < @MAXDATE
)
SELECT
  "name",
  dates.date_axis AS "start",
  dates.date_axis AS "end"
FROM dates
  INNER JOIN example
    ON dates.date_axis BETWEEN example."start" AND example."end"
OPTION(MAXRECURSION 32767)
;

这现在适用于原始表中任意数量的行，因此我修改了最大递归选项以允许广泛的日期范围(但如果您走这条路，您可能希望设置一个更合理的限制(

<小时 />

完整的数据库<>小提琴可在以下位置找到：

https://dbfiddle.uk/6ThD5Xbz

"日历表"CTE

回答问题

相关内容

最新更新

热门标签：