时间戳

首页 > 解决方案 > 三张表的联合和左连接

mysql - 三张表的联合和左连接

问题描述

我有三张桌子A，B，AB。

三张桌子

表 A 有 60 个手机号码，每个手机号码都有一个 scoreA。表 B 有 40 个手机号码，每个手机号码都有一个 scoreB。表 AB 有来自 A 的 40 个手机号码和来自 B 的 10 个手机号码和一个 scoreAB。

如果一个手机只存在于表 A 中，它只会有 scoreA。

如果一个手机只存在于表 B 中，它只会有 scoreB。

如果 A 和 B 中都存在移动设备，则它将具有 scoreA、scoreB 和 scoreAB。

我想创建一个具有以下架构的新表：

mobile    scoreA    scoreB    scoreAB

其中，从表A的60个手机号码中，表AB中不存在的20个的scoreB、scoreAB应该为空值。表 AB 中存在的其余 40 个应具有所有三个分数。

从表 B 中的 40 个手机号码中，表 AB 中不存在的 30 个应具有 scoreA、scoreAB 的空值。表 AB 中存在的其余 10 个应具有所有三个分数。

到目前为止，我做了以下事情：

spark.sql(""" SELECT A.mobile as mobile_A
          FROM A 
          UNION
          SELECT B.mobile as mobile_B 
          FROM B
          UNION
          SELECT AB.mobile as mobile_AB
          FROM AB 
        """).createOrReplaceTempView('union_table')

然后，

spark.sql(""" SELECT u.mobile_A, 
                 A.score as scoreA, B.score as scoreB, AB.score as scoreAB
              FROM union_table as u
              LEFT JOIN A
              ON A.mobile = u.mobile_n 
              LEFT JOIN B
              ON B.mobile = u.mobile_n
              LEFT JOIN C
              ON C.mobile = u.mobile_n
      """).createOrReplaceTempView('scores_combined_table')

我没有得到正确的结果。上面的查询有什么问题？

标签： mysqlsqlleft-joinunion

解决方案

一种方法使用union all和聚合：

select mobile,
       max(scorea) as scorea,
       max(scoreb) as scoreb,
       max(scoreab) as scoreab
from ((select mobile, scorea, null as scoreb, null as scoreab
       from a
      ) union all
      (select mobile, null as scorea, scoreb, null as scoreab
       from b
      ) union all
      (select mobile, null as scorea, null as scoreb, scoreab
       from ab
      )
     ) ab
group by mobile;

full join这是实现MySQL 不支持的一个版本。另一种方法使用left join：

select *
from (select mobile from a
      union -- on purpose to remove duplicates
      select mobile from b
      union 
      select mobile from ab -- just in case there are other mobiles here
     ) m left join
     a
     using (mobile) left join
     b
     using (mobile)
     left join
     c
     using (mobile);

推荐阅读