如何在 sql server 2012 中找到動態生成的時間屬性的總和

![樣本數據]](https://i.stack.imgur.com/xZOlQ.png)

我有一個有兩列（TASKID和Dateadded）的表。我試圖找出時間之間的差異，並想計算 diff （時間）屬性的總和。

select taskid,dateAdded,CAST((dateadded -lag(dateadded) over (order by dateadded)) as time )as diff  
from xyz where taskid=21240923 

有了這個語句，得到了時間之間的差異，但我無法對動態生成的時間列求和diff。

任何機構都可以建議實現這一目標嗎？

https://www.blogger.com/blogger.g?blogID=1341056964785354138#editor/target=post;postID=5797398234800649517;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

將您目前的查詢用作 CTE（通用表表達式）並選擇具有聚合函式的表單，例如：

WITH TimeDiffs AS (<your query>)
SELECT   taskid
      , SUM(diff)
FROM     TimeDiffs
GROUP BY taskid

（假設您想要每個任務的總數，您的問題在細節上不是很清楚）。

您還可以將現有查詢用作派生表：

SELECT taskid
    , SUM(diff)
FROM   (<your query>) AS TimeDiffs
GROUP BY taskid

這將產生相同的結果，儘管 CTE 安排通常最終更具可讀性，因此更易於維護。

您可以使用 CTE 以及 DATEDIFF 和 DATEADD 函式輕鬆檢索它：

設置測試數據：

CREATE TABLE #TimeData
(
   TaskID INT,
   DateAdded DATETIME
)
GO
DECLARE @Bucket INT = 1,
   @Counter INT = 1

WHILE @Bucket < 5
BEGIN
   WHILE @Counter < 10
   BEGIN
       INSERT INTO #TimeData (TaskID, DateAdded)
       VALUES (@Bucket, DATEADD(SECOND, @Counter, GETDATE()))

       SET @Counter = @Counter + 1
   END
   SET @Counter = 0
   SET @Bucket = @Bucket + 1
END

詢問：

CTE 創建一個偽表，並使用 DATEDIFF 和 LAG 獲取每行與前一行之間的時間差（以秒為單位）。在主 SELECT 中，我們對 TimeDiff 求和（以秒為單位）並將這些秒數加到 0 以獲得表示為時間值的總時間差。

;WITH TimeDiff AS (
   SELECT TaskID,
       DateAdded,
       COALESCE(DATEDIFF(SECOND, LAG(DateAdded) OVER (PARTITION BY TaskID ORDER BY DateAdded), DateAdded), 0) AS TimeDiff
   FROM #TimeData
)

SELECT TaskID,
   MIN(DateAdded) AS DateStarted,
   MAX(DateAdded) AS DateEnded,
   CAST(DATEADD(SECOND, SUM(TimeDiff), 0) AS TIME) AS TotalTimeDiff
FROM TimeDiff
GROUP BY TaskID

結果：

TaskID  |   DateStarted             |   DateEnded               |   TotalTimeDiff
------------------------------------------------------------------------------------
1       |   2019-10-15 15:30:20.820 |   2019-10-15 15:30:28.820 |   00:00:08.0000000
2       |   2019-10-15 15:30:19.820 |   2019-10-15 15:30:28.820 |   00:00:09.0000000
3       |   2019-10-15 15:30:19.820 |   2019-10-15 15:30:28.820 |   00:00:09.0000000
4       |   2019-10-15 15:30:19.820 |   2019-10-15 15:30:28.820 |   00:00:09.0000000

引用自：https://dba.stackexchange.com/questions/250563