SQL -按日期间隔聚合数据

Question

我在连接两个表示用户状态更改日期间隔的表时遇到了问题。两个表中的ID相同。

Table_A

ID  StatusA    FromA              ToA
1   Active     01/01/2020 10:00   01/01/2020 11:30
1   NonActive  02/01/2020 09:00   03/01/2020 11:00
2   Active     01/01/2020 10:00   01/01/2020 11:30
3   Active     02/01/2020 09:30   02/01/2020 15:50

Table_B

ID  StatusB    FromB               ToB
1   Chatting   01/01/2020 10:02   01/01/2020 10:15
1   Calling    01/01/2020 10:10   01/01/2020 10:20
2   Awaiting   02/01/2020 10:00   02/01/2020 10:15
2   Calling    02/01/2020 10:16   02/01/2020 10:20
3   Awating    02/01/2020 09:30   02/01/2020 15:50
1   Awating    02/01/2020 09:00   03/01/2020 11:00

我想要得到的是这样一个表：

ID  StatusA    FromA              ToA               StatusB   FromB              ToB
1   Active     01/01/2020 10:00   01/01/2020 11:30  Chatting  01/01/2020 10:02   01/01/2020 10:15
1   Active     01/01/2020 10:00   01/01/2020 11:30  Calling   01/01/2020 10:10   01/01/2020 10:20
2   Active     01/01/2020 10:00   01/01/2020 11:30  Awaiting  02/01/2020 10:00   02/01/2020 10:15
2   Active     01/01/2020 10:00   01/01/2020 11:30  Calling   02/01/2020 10:16   02/01/2020 10:20

我在最开始的时候写的查询：

SELECT Table_A.ID, 
Table_A.StatusA AS "Primary_Status", 
Table_A.FromA, 
Table_A.ToA,
Table_B.StatusB AS "Secondary_Status",
Table_B.FromB,
Table_B.ToB,
FROM TableA 
INNER JOIN Table_B ON Table_A.ID = Table_B.ID
WHERE ((Table_B.startTime BETWEEN Table_A.FromA AND Table_A.ToA)
or (Table_B.FromB <= Table_A.FromA AND Table_B.ToB <= Table_A.ToA)
or (Table_B.FromB <= Table_A.FromA AND Table_B.ToB >= Table_A.ToA)
or (Table_B.FromB BETWEEN Table_A.FromA AND Table_A.ToA AND Table_B.ToB >= Table_A.ToA) 
);

提前感谢！

编辑:每个ID唯一地标识一个用户。因此，Table_A中ID =1的用户与Table_B中ID =1的用户相同

EDIT_02:多亏了戈登·林诺夫解决方案，我现在

SELECT Table_A.ID, 
Table_A.StatusA AS "Primary_Status", 
Table_A.FromA, 
Table_A.ToA,
Table_B.StatusB AS "Secondary_Status",
Table_B.FromB,
Table_B.ToB,
FROM TableA 
INNER JOIN Table_B ON (Table_A.ID = Table_B.ID AND Table_B.FromB >= Table_A.FromA AND Table_B.ToB <= Table_A.ToA)

这个查询是有效的，但我可能会遇到这样的情况

b.FromB <= a.FromA && b.ToB <= a.ToA
b.FromB >= a.FromA && b.ToB >= a.ToA
b.FromB <= a.FromA && b.ToB >= a.ToA

Answer 1

您似乎只需要一个在时间列上具有正确条件的join：

SELECT a.ID, a.StatusA AS "Primary_Status", a.FromA, a.ToA,
       b.StatusB AS "Secondary_Status", b.FromB, b.ToB,
FROM TableA a JOIN
     Table_B b
     ON a.ID = b.ID AND
        b.FromB >= a.FromA AND
        b.ToB <= b.FromB;