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'
你有一个语法问题,因为 我只会使用 然后,您可以使用子查询对它们进行计数: I am trying to get the count of distinct people of the shaded region. Table structure is as follows: HIVE Query: Error: missing EOF at 'join' near 'null' You have a syntax problem because I would just use You can then count them with a subquery:
这篇关于3个表的左外连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!其中
遵循子句中的,并且给定的
select
只有一个,其中$ c> $ 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
customer key
A234 1
A345 4
A12 5
A989 6
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;
where
follows the from
clause, and a given select
has only one where
.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;
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