3个表的左外连接 [英] Left outer join of 3 tables

问题描述

我试图得到阴影区域的不同人数。

表结构如下：

 客户键
 A234 1 
 A345 4 
 A12 5 
 A989 6 
  

HIVE查询：

  select count（distinct（a.customer））
 from（
 select * 
 from cust 
 where key in（1,2,3））c 
离开外部连接（
从cust $ b $中选择* 
其中，键入（4,5））a对a.customer = c.customer其中c.customer为空
加入
（从cust 
中选择* 
，其中c.customer = d.customer和d.customer中的键（6,7,8,9））d为空; 
  

错误：

join'near'null'

解决方案

你有一个语法问题，因为其中遵循子句中的，并且给定的 select 只有一个，其中 $ c $。



我只会使用 group by 和具有。为了获得客户：



 从客户c中选择c.customer 
 c c。b $ b中的c.customer $具有总和的b $ b（键入（1，2，3）然后1 else 0结束的情况）> 0和
 sum（键入（4,5,6,7,8,9）然后1 else 0 end）的情况= 0; 
  

然后，您可以使用子查询对它们进行计数：



  select count（*）
 from（选择c.customer $ b $ from cust c 
 group by c.customer 
 having sum （1，2，3）然后1 else 0 end）> 0和
 sum（键入（4,5,6,7,8,9）然后1 else 0结束的情况） = 0 
）c 
  




I am trying to get the count of distinct people of the shaded region.

Table structure is as follows:

    customer       key
    A234            1
    A345            4
    A12             5
    A989            6

HIVE Query:

    select count(distinct(a.customer)) 
    from (
        select *
         from cust
         where key in (1,2,3)) c 
    left outer join (
        select *
         from cust
         where key in (4,5)) a on a.customer= c.customer where c.customer is null
         join
            (select *
             from cust
             where key in (6,7,8,9)) d on c.customer = d.customer and d.customer is null;

Error:

missing EOF at 'join' near 'null'

解决方案

You have a syntax problem because where follows the from clause, and a given select has only one where.

I would just use group by and having. To get the customers:

select c.customer
from cust c
group by c.customer
having sum(case when key in (1, 2, 3) then 1 else 0 end) > 0 and
       sum(case when key in (4, 5, 6, 7, 8, 9) then 1 else 0 end) = 0;

You can then count them with a subquery:

select count(*)
from (select c.customer
      from cust c
      group by c.customer
      having sum(case when key in (1, 2, 3) then 1 else 0 end) > 0 and
             sum(case when key in (4, 5, 6, 7, 8, 9) then 1 else 0 end) = 0
    ) c

这篇关于3个表的左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！