查找時間開始日期和結束日期在兩個表中重疊

使用 SQL Server 2016，我有兩個表，都包含事件開始和事件結束的日期時間列。

我的目標是從表 1 中查看事件時間，其中表 2 中存在時間間隔。表 1 將是連續的，這意味著沒有時間重疊，但由于不同用戶在不同時間進入，表 2 將有重疊。

在我的例子table1.eventId = 3中，table2 有很大的差距。我想要找到它的方法。我看過使用交叉連接，然后在帶有子查詢的選擇中使用一些 case 陳述句，也許是遞回 CTE？

源資料示例

表格1

 eventId| startDateTime       |   endDateTime
---------------------------------------------------
1       | 10-29-2021 00:01:00 | 10-29-2021 00:20:00
2       | 10-29-2021 00:20:00 | 10-29-2021 00:31:00
3       | 10-29-2021 00:31:00 | 10-29-2021 01:04:00
4       | 10-29-2021 01:04:00 | 10-29-2021 03:15:00
5       | 10-29-2021 03:15:00 | 10-29-2021 04:02:00

表 2

 eventId| user  |startUserDateTime   |   endUserDateTime
------------------------------------------------------
1       | user1 |10-29-2021 00:01:00 | 10-29-2021 00:31:05
1       | user2 |10-29-2021 00:01:15 | 10-29-2021 00:31:10
3       | user1 |10-29-2021 01:04:00 | 10-29-2021 03:18:00
4       | user2 |10-29-2021 01:04:00 | 10-29-2021 04:02:02

編輯：經過考慮，我希望最終的輸出是這樣的，每個 table1.eventId 都會有來自 table2 的用戶總用戶分鐘數。userTotalMinutes 將是每個 table1.eventId 的時間范圍內所有用戶分鐘的總和

eventId| startDateTime       |   endDateTime | userTimeTotalMinutes | 
    -----------------------------------------------------------
    1       | 10-29-2021 00:01:00 | 10-29-2021 00:20:00 | 40
    2       | 10-29-2021 00:20:00 | 10-29-2021 00:31:00 | 22
    3       | 10-29-2021 00:31:00 | 10-29-2021 01:04:00 | 0
    4       | 10-29-2021 01:04:00 | 10-29-2021 03:15:00 | 142
    5       | 10-29-2021 03:15:00 | 10-29-2021 04:02:00 | 47 

uj5u.com熱心網友回復：

我不確定這是最好的方法，但我認為它非常直觀，您可以以此為基礎：

SELECT t1.*, 
 (SELECT sum(v) 
 FROM 
  (SELECT datediff(minute, CASE WHEN t2.startdatetime > t1.startdatetime THEN t2.startdatetime ELSE t1.startdatetime END, CASE WHEN t2.enddatetime > t1.enddatetime THEN t1.enddatetime ELSE t2.enddatetime END) v 
  FROM table2 t2) a 
 WHERE v > 0)
FROM table1 t1

對于 table1 中的每一行，它對 table2 中的相關范圍求和。通過計算較大的 startdatetime 和較小的 enddatetime 之間的差異（然后排除沒有重疊的負值）來確定相關范圍。

uj5u.com熱心網友回復：

幾樣東西：

1. 您計算時間的方式不清楚，例如如果 table1.eventId =1 發生在 00:01:00 和 00:20:00 之間（持續 20 分鐘），現在我認為這是 19 分鐘
2. 對于 eventId 4，user1 和 user2 始終可用，請檢查一次您想要的有問題的輸出。
3. 以下是實作您想要的目標的良好開端，請根據需要進行修改。
4. 請有人幫我用縮進格式化查詢，我無法以我想要的方式呈現，下面是我可以用這個編輯器做的最好的事情。（抱歉）

`SELECT a.eventId,a.startDateTime,a.endDateTime,

SUM(
    CASE 
        WHEN b.endUserDateTime <= a.startDateTime THEN 0
        WHEN b.startUserDateTime <= a.startDateTime and b.endUserDateTime <= a.endDateTime THEN DATEDIFF(mi,a.startDateTime,b.endUserDateTime)
        WHEN b.startUserDateTime <= a.startDateTime and b.endUserDateTime > a.endDateTime THEN DATEDIFF(mi,a.startDateTime,a.endDateTime)
        WHEN b.startUserDateTime > a.endDateTime THEN 0
        WHEN b.startUserDateTime > a.startDateTime and b.endUserDateTime <= a.endDateTime THEN DATEDIFF(mi,b.startUserDateTime,b.endUserDateTime)
        WHEN b.startUserDateTime > a.startDateTime and b.endUserDateTime > a.endDateTime THEN DATEDIFF(mi,b.startUserDateTime,a.endDateTime)
    END ) 

FROM Table1 a INNER JOIN Table2 b ON 1=1 GROUP BY a.eventId,a.startDateTime,a.endDateTime`

uj5u.com熱心網友回復：

如果您不喜歡 中的子查詢SELECT，您可以使用 CTE 以不同的方式完成相同的目標。

DECLARE @t1 TABLE (eventID INT, startDateTime DATETIME, endDateTime DATETIME)
INSERT INTO @t1
VALUES
(1, '10-29-2021 00:01:00', '10-29-2021 00:20:00'),
(2, '10-29-2021 00:20:00', '10-29-2021 00:31:00'),
(3, '10-29-2021 00:31:00', '10-29-2021 01:04:00'),
(4, '10-29-2021 01:04:00', '10-29-2021 03:15:00'),
(5, '10-29-2021 03:15:00', '10-29-2021 04:02:00')

DECLARE @t2 TABLE (eventID INT, userID VARCHAR(10), startDateTime DATETIME, endDateTime DATETIME)
INSERT INTO @t2
VALUES
(1, 'user1', '10-29-2021 00:01:00', '10-29-2021 00:31:05'),
(1, 'user2', '10-29-2021 00:01:15', '10-29-2021 00:31:10'),
(3, 'user1', '10-29-2021 01:04:00', '10-29-2021 03:18:00'),
(4, 'user2', '10-29-2021 01:04:00', '10-29-2021 04:02:02');

WITH CTE_Alias
AS
(
    SELECT t2.eventID,
        datediff(minute, CASE WHEN t2.startDateTime > t1.startDateTime   THEN t2.startDateTime ELSE t1.startDateTime END, 
        CASE WHEN t2.endDateTime > t1.endDateTime THEN t1.endDateTime ELSE t2.endDateTime END) AS v 
  FROM @t2 t2
    INNER JOIN @t1 t1 ON t2.eventID = t1.eventID
)

SELECT t.eventID,
    t.startDateTime,
    t.endDateTime,
    ISNULL(SUM(v),0) AS userTimeTotalMinutes
FROM @t1 t
    LEFT JOIN CTE_Alias c ON t.eventID = c.eventID
GROUP BY t.eventID, t.startDateTime, t.endDateTime

標籤：sql sql-server 查询语句

上一篇：為什么這個執行計劃中Sort運算子的成本如此之高？

下一篇：我們如何在SQLServer中使用@CodeMask字串動態更改此@ClientIDCode