在Do while SQL Server中的do while

Question

我刚刚创建了一个计划项目，需要帮助..

我有一个日程表数据

ID      Programme        Start      End         Division
----------------------------------------------------------
M001    Math             1/1/2017   20/01/2017  Math
M002    Aljabar          2/2/2017   20/02/2017  Math
E001    Conversation     3/1/2017   25/01/2017  English
E002    Vocabs           3/1/2017   20/02/2017  English

我需要让它看起来像这样：

ID        Date     
------------------
M001     1/1/2017
M001     2/1/2017
M001     3/1/2017
so on until 20/1/2017
M002     2/2/2017
M002     3/2/2017
so on until 20/02/2017
E001     3/1/2017
E001     4/1/2017
so on until 25/01/2017
E002     3/1/2017
E002     4/1/2017
so on until 20/02/2017

谢谢

Answer 1

一种方法是使用递归CTE：

with cte as (
      select id, start as dte, end
      from t
      union all
      select id, dateadd(day, 1, dte), end
      from t
      where dte < end
     )
select id, dte
from cte
order by id, dte;

如果您有超过100天的时间，则需要使用MAXRECURSION选项。

注意:在假设这些列不是真实名称的情况下，上面的列保留了问题中指定的列。显然，end是一个保留字(将来start也可能是保留字)，所以如果它们是实际的列名，就应该对它们进行转义。

Answer 2

你需要一个日程表来做这件事。我更喜欢在我的数据库中创建一个物理日历表，并在这样的情况下使用它。下面是使用计数表法动态生成日期的一种方法

DECLARE @minDate DATETIME

SELECT @minDate = Min([Start])
FROM   Yourtable

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv3)
SELECT ID,
       Dateadd(DD, n - 1, @minDate)
FROM   Tally t
       JOIN Yourtable a
         ON Dateadd(DD, t.n - 1, @minDate) BETWEEN [Start] AND [End]
ORDER  BY ID,n; 

如果您想了解更多关于理货表的信息，请查看此链接Tally Tables in T-SQL