sql - 将包含时间范围的行切成多行(棘手的 sql 问题)

Question

首先，我无法指定标题来包含我的整个问题。我很抱歉。

我的目标是在更改某些列值的同时增加行数，直到满足特定条件。是的，糟糕的解释。

我有:

CREATE TABLE OldTable 
(
    fullname varchar(50) NOT NULL,
    unit varchar(50) NOT NULL,
    code int NOT NULL,
    shift_datetime datetime NOT NULL,
    timespan int NOT NULL
)

INSERT INTO OldTable (fullname, unit, code, shift_datetime, timespan) 
VALUES ('John Smith', 'Heroes', '239', '2020-03-04 13:35:00.000', '55'
'Tom Cruise', 'Heroes', '213', '2020-03-05 09:13:00.000', '8'
'My Mom', 'Heroes', '483', '2020-02-01 08:57:00.000', '16')

生成此表，OldTable:

| fullname   | unit   | code | shift_datetime          | timespan |
+------------+--------+------+-------------------------+----------+
| John Smith | Heroes | 239  | 2020-03-04 13:35:00.000 |      55  |
| Tom Cruise | Heroes | 213  | 2020-03-05 09:13:00.000 |       8  |
| Mom        | Heroes | 483  | 2020-02-01 08:57:00.000 |      16  |

我想创建这个 NewTable:

| fullname   | unit   | code | shift_datetime          | timespan |
+------------+--------+------+-------------------------+----------+
| John Smith | Heroes | 239  | 2020-03-04 13:35:00.000 |      15  |
| John Smith | Heroes | 239  | 2020-03-04 13:50:00.000 |      15  |
| John Smith | Heroes | 239  | 2020-03-04 14:05:00.000 |      15  |
| John Smith | Heroes | 239  | 2020-03-04 14:20:00.000 |      10  |
| Tom Cruise | Heroes | 213  | 2020-03-05 09:13:00.000 |       8  |
| Mom        | Heroes | 483  | 2020-02-01 08:57:00.000 |      15  |
| Mom        | Heroes | 483  | 2020-02-01 08:12:00.000 |       1  |

所以问题的表述更像是

如果span > 15，则分成floor(span/15)行，每行span = 15，同时增加shift_datetime 每添加一行 15 分钟，最后添加 span = span %% 15 的最后一行并将这些 span %% 15 分钟添加到该“循环”中最大的 shift_datetime 值。

如果您对如何“解决”这个问题有任何想法，我将不胜感激。我不是只是在寻找解决方案，而是非常想寻求有关如何处理此类问题的建议。

我可以通过循环在 R 中执行此操作，因此我假设这也可以通过 SQL 中的循环来完成。但是，我很想听听其他选择或想法。

Answer 1

听起来递归 CTE 可以提供帮助:

with cte as (
      select fullname, unit, code, shift_datetime, 
             (case when timespan > 15 then 15 else timespan end) as timespan,
             timespan as time_remaining, 1 as lev
      from oldtable
      union all
      select fullname, unit, code,
             dateadd(minute, 15, shift_datetime),
             (case when time_remaining > 15 then 15 else time_remaining end) as timespan,
             time_remaining - 15, lev + 1
      from cte
      where time_remaining > 15
     )
select * 
from cte
order by 1, lev;

Here是一个数据库<> fiddle 。