php - 如何从两个表中找到唯一变量的必要值

Question

1)我有一个名为“plan_info_upload”的表，如下所示:

site_id site_name   2G_bw   3G_bw   route_path
1       MBCGP1      11      30      MBLMA1>MBSMGR
2       BOPBG2      12      22      BOPBG2>BOBET16
3       BOPCB1      11      0       BOBET16>BOGBT1>BOPBG2>BOBET16
4       BOSBB1      14      25      BOSBB1>BOKDG1>BOBET16>BOGBT1

2) 另一个名为“hop_without_router”的表，如下所示:(将第一个表中的route_path划分为跃点)

hop_id  hop_1          hop_2         hop_3..... hop_9    site_name  hop_count
1      MBLMA1>MBSMGR                                     MBCGP1       1
2      BOPBG2>BOBET16                                    BOPBG2       1
3      BOBET16>BOGBT1 BOGBT1>BOPBG2  BOPBG2>BOBET16      BOPCB1       3      
4      BOSBB1>BOKDG1  BOKDG1>BOBET16 BOBET16>BOGBT1      BOSBB1       3

3)我从第二个表中找到了 unique_hop 。查询是...

$sql = "SELECT DISTINCT (hops.hop_route) FROM (
                SELECT DISTINCT hop_1 as hop_route FROM hop_without_router
                 UNION 
                SELECT DISTINCT hop_2 as hop_route FROM hop_without_router
                 UNION 
                SELECT DISTINCT hop_3 as hop_route FROM hop_without_router
                 UNION 
                SELECT DISTINCT hop_4 as hop_route FROM hop_without_router
                 UNION 
                ......
                SELECT DISTINCT hop_9 as hop_route FROM hop_without_router) as hops";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
    while($row = $result->fetch_assoc()) 
    {
       $hop_route = $row['hop_route'];
       echo $hop_route;
    }
}

结果如下:

serial_id   unique_hop
1           MBLMA1>MBSMGR
2           BOPBG2>BOBET16
3           BOBET16>BOGBT1
4           BOGBT1>BOPBG2
5           BOSBB1>BOKDG1
6           BOKDG1>BOBET16

4)现在我已经找到了每跳有多少个 2G_bw 和 3G_bw 以及它们的总带宽。结果会是这样的。

unique_hop     no_of_2G   no_of_3G   total_2G_bw   total_3G_bw
MBLMA1>MBSMGR   1          1          11           30
BOPBG2>BOBET16  2          2          23           52
BOBET16>BOGBT1  2          2          25           55
BOGBT1>BOPBG2   1          0          11           0
BOSBB1>BOKDG1   1          1          14           25
BOKDG1>BOBET16  1          1          14           25

我完成了第三步。但无法匹配或完成第4步。如何使用表格完成第四步？请有人帮我完成这个。提前致谢。

**一个唯一的跃点可能有多个 site_name。 &有可能一跳没有任何3g带宽。

Answer 1

可能是这样的

SELECT hops.hop_route,count(plan_info_upload.*) as no_of_2G, SUM(plan_info_upload.2G_bw) as total_2G_bw FROM(
SELECT DISTINCT (hops.hop_route) as hop_route FROM (
                SELECT DISTINCT hop_1 as hop_route FROM hop_without_router
                 UNION 
                SELECT DISTINCT hop_2 as hop_route FROM hop_without_router
                 UNION 
                SELECT DISTINCT hop_3 as hop_route FROM hop_without_router
                 UNION 
                SELECT DISTINCT hop_4 as hop_route FROM hop_without_router
                 UNION 
                ......
                SELECT DISTINCT hop_9 as hop_route FROM hop_without_router) as hops ) as unique_hops )  LEFT JOIN plan_info_upload ON hops.hop_route = plan_info_upload.route_path