每次遞回將SQL遞回限制為單行-有解無憂

我有兩張桌子：

事件（EventId INT，StartDate DATETIME）

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

原因（CauseId INT、EventId INT、分鐘 INT）

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

對于 and 的每個組合，EventId我CauseId需要生成一個StartDateand 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 似乎也不起作用，我不允許使用TOPorAGGREGATE并且看不到如何在沒有它的情況下一次處理一個行。例如

-- 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

uj5u.com熱心網友回復：

也許視窗功能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

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/456561.html

標籤：sql sql服务器递归查询

上一篇：將選定表用于兩組連接

下一篇：SQL-將一列資料轉換為一行