sql-server - 将时间频率拆分为行

Question

我正在尝试将具有开始时间、结束时间、频率和持续时间的时间频率拆分为单独的行。以下是一些示例数据:

+------+------------+----------+-----------------+---------------+
| Name | Start_Time | End_Time | Frequency_Hours | Duration_Mins |
+------+------------+----------+-----------------+---------------+
| A    | 08:00:00   | 18:00:00 |               2 |             2 |
| B    | 00:00:00   | 23:59:59 |               1 |             5 |
| C    | 00:00:00   | 23:59:59 |               4 |            15 |
+------+------------+----------+-----------------+---------------+

可以使用以下查询创建:

DECLARE @Tmp AS TABLE(Name VARCHAR(128)
                     ,Start_Time VARCHAR(8)
                     ,End_Time VARCHAR(8)
                     ,Frequency_Hours INT
                     ,Duration_Mins INT)

INSERT INTO @Tmp VALUES ('A','08:00:00', '18:00:00', 2,2)
                       ,('B','00:00:00', '23:59:59', 1,5)
                       ,('C','00:00:00', '23:59:59', 4,15)

这是我想要的输出(然后我将使用它来驱动甘特图可视化):

+------+------------+----------+
| Name | Start_Time | End_Time |
+------+------------+----------+
| A    | 08:00:00   | 08:02:00 |
| A    | 10:00:00   | 10:02:00 |
| A    | 12:00:00   | 12:02:00 |
| A    | 14:00:00   | 14:02:00 |
| A    | 16:00:00   | 16:02:00 |
| A    | 18:00:00   | 18:02:00 |
| B    | 00:00:00   | 00:05:00 |
| B    | 01:00:00   | 01:05:00 |
| B    | 02:00:00   | 02:05:00 |
| B    | 03:00:00   | 03:05:00 |
| B    | 04:00:00   | 04:05:00 |
| B    | 05:00:00   | 05:05:00 |
| B    | 06:00:00   | 06:05:00 |
| B    | 07:00:00   | 07:05:00 |
| B    | 08:00:00   | 08:05:00 |
| B    | 09:00:00   | 09:05:00 |
| B    | 10:00:00   | 10:05:00 |
| B    | 11:00:00   | 11:05:00 |
| B    | 12:00:00   | 12:05:00 |
| B    | 13:00:00   | 13:05:00 |
| B    | 14:00:00   | 14:05:00 |
| B    | 15:00:00   | 15:05:00 |
| B    | 16:00:00   | 16:05:00 |
| B    | 17:00:00   | 17:05:00 |
| B    | 18:00:00   | 18:05:00 |
| B    | 19:00:00   | 19:05:00 |
| B    | 20:00:00   | 20:05:00 |
| B    | 21:00:00   | 21:05:00 |
| B    | 22:00:00   | 22:05:00 |
| B    | 23:00:00   | 23:05:00 |
| C    | 00:00:00   | 00:15:00 |
| C    | 04:00:00   | 04:15:00 |
| C    | 08:00:00   | 08:15:00 |
| C    | 12:00:00   | 12:15:00 |
| C    | 16:00:00   | 16:15:00 |
| C    | 20:00:00   | 20:15:00 |
+------+------------+----------+

我希望能够从中创建一个 View ，所以我尝试在没有游标或其他 cpu 密集型方法的情况下进行。

有什么想法吗？

谢谢，

丹.

Answer 1

你可以像这样使用递归cte

;WITH temp AS 
(
    SELECT t.Name, CAST(t.Start_Time AS time) AS CurrentStart_Time, dateadd(minute,t.Duration_Mins,CAST(t.Start_Time AS time)) AS CurrentEnd_Time, t.Frequency_Hours, CAST(t.End_Time AS time) AS End_Time
    FROM @Tmp t

    UNION ALL

    SELECT t.Name, dateadd(hour,t.Frequency_Hours,t.CurrentStart_Time), dateadd(hour,t.Frequency_Hours,t.CurrentEnd_Time), t.Frequency_Hours, t.End_Time
    FROM temp t
    WHERE t.CurrentStart_Time < t.End_Time AND t.CurrentStart_Time < dateadd(hour,t.Frequency_Hours,t.CurrentStart_Time) 
)

SELECT t.Name, t.CurrentStart_Time, t.CurrentEnd_Time
FROM temp t
ORDER BY t.Name
OPTION (MAXRECURSION 0)

演示链接:http://rextester.com/XJK25805