如何从两个表中找到唯一变量的必要值 [英] how to find necessary values of a unique variable from two table

问题描述

1) 我有一个名为plan_info_upload"的表，类似于:

1) I have a table named "plan_info_upload" which is like:

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)

2) & another table named "hop_without_router" which is like: (divide route_path from 1st table into hop)

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.查询是...

3) I find unique_hop from the 2nd table. the query is...

$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 每跳有 &他们的总带宽.结果会是这样.

4) Now I have find how how many 2G_bw & 3G_bw each hop has & their total bandwidth. The result will be like this.

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 步.如何使用表格完成第四步?请有人帮我完成这个.提前致谢.

I complete 3rd step. But can not match or complete the 4th step. How to complete 4th step using the tables? Please please can someone help me to complete this. Thanks in advance.

**一个唯一的跃点可能有多个站点名称.&有可能一跳没有任何3g带宽.

**One unique hop may have more than one site_name. & there is possible that one hop does not have any 3g bandwidth.

推荐答案

可能是这样的

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

这篇关于如何从两个表中找到唯一变量的必要值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！