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

I have a table, with types varchar, datetime, datetime:

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

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

What's a SQL query can I look up to find out how to make that table be?:

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

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

This is only ran once, and on a very small dataset. Optimization isn't necessary.

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

3 个解决方案

#1

May be you need a Recursive CTE.

可能你需要一个递归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

OUTPUT :

输出：

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

#2

You can do this with a recursive cte:

您可以使用递归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

However, I suggest creating a calendar table and joining to it:

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

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]

Demo of both queries: SQL Fiddle

演示两个查询：SQL Fiddle

#3

;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

#1