在以下业务场景中,我们有一张订单表,订单表中有产品的编号,我们需要匹配出产品所属的品牌名称。不同品牌的产品名称分别存放在不同的产品清单表中。
在MySQL等关系型数据库中,可以使用case when+in(子查询)
的方式来匹配订单表中的产品所属的品牌名称。
select product_code
,product_quantity
,case when product_code in (select product_code from table_a)
then 'BRAND_A'
when product_code in (select product_code from table_b)
then 'BRAND_B'
when product_code in (select product_code from table_c)
then 'BRAND_C'
else null
end as brand_name
from
table_order
但是在HIVE SQL中,如果执行该查询语句,就会报错,提示Currently SubQuery expressions are only allowed as Where Clause predicates
。
这个时候可以使用left join
来解决。
with tmp1 as
(
select product_code
,product_quantity
,case when t2.product_code is not null then 'BRAND_A'
else null
end as brand_name
from
table_order t1
left join table_a t2
on t1.product_code=t2.product_code
)
,tmp2 as
(
select product_code
,product_quantity
,case when t2.product_code is not null then 'BRAND_B'
else t1.brand_name
end as brand_name
from
tmp1 t1
left join table_b t2
on t1.product_code=t2.product_code
)
,tmp3 as
(
select product_code
,product_quantity
,case when t2.product_code is not null then 'BRAND_C'
else t1.brand_name
end as brand_name
from
tmp2 t1
left join table_c t2
on t1.product_code=t2.product_code
)
select product_code,product_quantity,brand_name from tmp3;