通过日期范围将两个查询合并，并获取连接中的首次出现

Question

假设我有一个名为“Calls”的SQLite表，如下所示：

index_call  start_datetime      nextcall_datetime
--------------------------------------------------
1           2024-01-01 08:00AM  2024-01-01 08:24AM
2           2024-01-01 08:24AM  2024-01-01 08:35AM
3           2024-01-01 08:35AM  2024-01-01 08:45AM

还有一个名为“Event”的表，内容如下：

id_event    id_datetime     
------------------------------ 
1           2024-01-01 08:04AM
2           2024-01-01 08:12AM
3           2024-01-01 08:25AM
4           2024-01-01 08:30AM

现在需要查询在每次通话期间发生的事件，但如果同一时间段内有多个事件，只取最后一个发生的时间点。

我尝试了以下查询，但它生成了两个表之间的笛卡尔积。

SELECT 
    calls.index_call,
    calls.start_datetime,
    calls.nextcall_datetime,
    event.id_datetime
FROM 
    calls AS calls 
LEFT JOIN 
    event AS event 
WHERE 
    event.id_datetime BETWEEN calls.start_datetime AND calls.nextcall_datetime

期望结果：

index_call  start_datetime      nextcall_datetime    id_datetime
-----------------------------------------------------------------------
1           2024-01-01 08:00AM  2024-01-01 08:24AM   2024-01-01 08:12AM
2           2024-01-01 08:24AM  2024-01-01 08:35AM   2024-01-01 08:30AM
3           2024-01-01 08:35AM  2024-01-01 08:45AM

Luchian Grigore · Answer

尝试像下面这样编写SQL查询：

SELECT    *
FROM      (
           Select      
               c.INDEX_CALL, 
               START_DATETIME, 
               NEXTCALL_DATETIME, 
               e.ID_EVENT, 
               e.ID_DATETIME,
               ROW_NUMBER() OVER(
                   Partition By c.INDEX_CALL 
                   Order By e.ID_DATETIME Desc
               ) AS "RN_ORDER"
           From        
               calls c
           Left Join   
               event e  
               ON(e.ID_DATETIME >= c.START_DATETIME And e.ID_DATETIME < c.NEXTCALL_DATETIME)
         )
WHERE     RN_ORDER = 1

Andy Thomas · Answer

您甚至可以使用NOT EXISTS子句，如下所示：

SELECT calls.index_call,
       calls.start_datetime,
       calls.nextcall_datetime,
       event.id_datetime
FROM calls
LEFT JOIN event
ON event.CreatedDate >= calls.start_datetime
AND event.CreatedDate < calls.nextcall_datetime
WHERE event.id_event IS NULL
   OR NOT EXISTS
      (SELECT 1 FROM event t
       WHERE t.CreatedDate < event.CreatedDate
       AND t.CreatedDate >= calls.start_datetime
       AND t.CreatedDate < calls.nextcall_datetime)