将 SQL 递归限制为每次递归的单行

Question

我有两个表：

事件(事件 ID INT、开始日期日期时间)

EventId | StartDate
-----------------------------
1000    | 2021-04-05 20:32:00

原因(原因 ID INT、事件 ID INT、分钟 INT)

CauseId | EventId | Minutes
-----------------------------
5000    | 1000    | 20
5001    | 1000    | 27
5002    | 1000    | 30

对于EventId和CauseId的每个组合，我需要生成一个StartDate和EndDate。

EventId | CauseId | Minutes | StartDate           | EndDate
------------------------------------------------------------------
1000    | 5000    | 20      | 2021-04-05 20:32:00 | 2021-04-05 20:52:00
1000    | 5001    | 27      | 2021-04-05 20:52:00 | 2021-04-05 21:19:00
1000    | 5002    | 30      | 2021-04-05 21:19:00 | 2021-04-05 21:49:00

LAG似乎不起作用，我需要参考前面的行计算来生成StartDate和相同的行计算来生成EndDate。

-- Not allowed.
SELECT e.EventId, c.CauseId, c.Minutes,
LAG(CalculatedEndDate, 1, DATEADD(MI, c.Minutes, e.StartDate)) OVER (PARTITION BY e.EventId ORDER BY c.CauseId) AS CalculatedStartDate,
DATEADD(MI, c.Minutes, CalculatedStartDate) AS CalculatedEndDate
FROM Event e
INNER JOIN Cause c ON c.EventId = e.EventId

递归 CTE 似乎也不起作用，我不允许使用TOP或AGGREGATE，并且看不到如何在没有它的情况下一次处理一行。

例如

-- Initial query
UNION ALL
-- Removed for brevity
INNER JOIN cte ON cte.EventId = e.EventId
INNER JOIN Cause c ON c.CauseId  = (SELECT MIN(CauseId) FROM Cause WHERE CauseId > cte.CauseId AND EventId = e.EventId)

或

-- Initial query
UNION ALL
-- Removed for brevity
INNER JOIN cte ON cte.EventId = e.EventId
CROSS APPLY (SELECT TOP 1 *
FROM Cause
WHERE EventId = e.EventId AND CauseId > cte.CauseId
ORDER BY CauseId ASC) c

Answer 1

也许sum() over()的窗口函数会在这里有所帮助

示例或 dbFiddle

Select A.*
,StartDate = dateadd(MINUTE
,sum(minutes) over (partition by A.EventID order by A.CauseID rows unbounded preceding ) 
- A.Minutes
,B.StartDate)
,EndDate    = dateadd(MINUTE
,sum(minutes) over (partition by A.EventID order by A.CauseID rows unbounded preceding ) 
,B.StartDate)
From Cause A
Join Event B on A.EventID=B.EventID

结果

CauseId EventId Minutes StartDate               EndDate
5000    1000    20      2021-04-05 20:32:00.000 2021-04-05 20:52:00.000
5001    1000    27      2021-04-05 20:52:00.000 2021-04-05 21:19:00.000
5002    1000    30      2021-04-05 21:19:00.000 2021-04-05 21:49:00.000