mysql - 如何查询链接到 MySQL 中具有特定 ID 的另一行的行？

Question

我正在尝试查询两个不同的表(CALL_HISTORY 和 HUB_DIRECTORY)以查找在“hub store”和“spoke store”之间进行的所有通话记录'.每个调用都有一个 CallID 字段，并使用发起调用的商店的 ID 创建一个条目，然后为每个接收调用的商店创建一个单独的条目，这些都具有 id接收它们的商店。所以它们都有相同的 CallID，但商店 ID (DID) 各不相同。

问题是并非每个调用都在中心及其辐射之间，因此我需要将其过滤掉以仅查找这些记录。

Sample Call Data

RecordID | CallID  | DID   | CallDirection | StartTime 
--------------------------------------------------------
1563486  | 255429  | 492   | Initiated     | 1520870539
1563487  | 255429  | 849   | Received      | 1520870539
1563484  | 255430  | 1098  | Initiated     | 1520870562
1563485  | 255430  | 1098  | Received      | 1520870562
1563482  | 255431  | 307   | Initiated     | 1520870567
1563483  | 255431  | 1013  | Received      | 1520870567
1563506  | 255432  | 1108  | Initiated     | 1520870580
1563509  | 255432  | 1108  | Received      | 1520870580

在这里您可以看到一个调用示例，突出显示的 CallID 组位于集线器及其分支之间，其余则不是。中心和辐射在 HUB_DIRECTORY 中链接在一起，如下所示:

HUB_DIRECTORY SAMPLE

HubStore | HubDID | SpokeStore | SpokeDID
-----------------------------------------
4        | 37     | Store0004  | 37
4        | 37     | Store0522  | 470
7        | 1083   | Store0007  | 1083
7        | 1083   | Store1000  | 714
7        | 1083   | Store1055  | 759
12       | 38     | Store0012  | 38
12       | 38     | Store1063  | 758
13       | 45     | Store0013  | 45
13       | 45     | Store0337  | 296
13       | 45     | Store1012  | 724

HubDID 和SpokeDID 字段与CALL_HISTORY 中的DID 相同。因此，我希望查询 HUB_DIRECTORY 表中存在发起调用 DID 的调用，作为 HubDID 或 SpokeDID 及其 CallID 也有一个记录，其 DID 与适当的 hub/spoke 相匹配。

我的最终目标是这样的:

HUB        | Spoke      | Initiated | Received
-----------------------------------------------
Store.0004 | Store.0522 |   304     |   723

我相信我将需要使用 UNION 来获取带有 hub 或 spoke 的行，但我无法完全理解如何完成此操作。

Answer 1

我认为这个查询会给你想要的结果。它适用于您提供的有限样本数据。

select h1.hubstore, h1.hubdid,
       h1.spokestore, h1.spokedid,
       count(distinct if(c2.recordid is null or c1.did!=h1.hubdid, null, c1.recordid)) as initiated, 
       count(distinct if(c2.did!=h1.hubdid, null, c2.recordid)) as received     
from hub_directory h1 
left join (select * from call_history where calldirection='Initiated') c1 
    on c1.did=h1.hubdid or c1.did=h1.spokedid
left join (select * from call_history where calldirection='Received') c2 
    on c2.callid = c1.callid and c2.did=if(c1.did=h1.hubdid, h1.spokedid, h1.hubdid)
group by h1.hubstore, h1.spokestore

基于您的 fiddle 中的新示例数据，这个查询给出

hubstore    spokestore  initiated   received
355         Store0355   0           0
355         Store0362   0           0
355         Store0655   0           0
357         Store0233   1           2
357         Store0357   0           0
360         Store0360   0           0
360         Store0868   0           0
360         Store1091   0           0
363         Store0363   0           0
363         Store1462   1           0
363         Store1507   1           0
363         Store2507   0           0