sql关联 on后面加条件

最新推荐文章于 2024-08-02 04:04:50 发布

TURBODW

最新推荐文章于 2024-08-02 04:04:50 发布

阅读量4.8k

点赞数 2

分类专栏： sql 文章标签： hive sql on 多条件

本文链接：https://blog.csdn.net/weixin_42861725/article/details/102921779

版权

本文介绍了在Hive中处理SQL关联的两种特殊需求：一是根据`qq.level_rank`条件选择不同字段进行关联；二是处理关联条件中有NULL值的情况。在MySQL等数据库中可以使用CASE语句，但在Hive中需要采取其他策略，如使用NVL函数避免NULL值导致的无结果问题。

摘要由CSDN通过智能技术生成

记两则需求SQL关联注意事项

需求一: 两个表分条件选择join

两个表字段如下

表ci:

c_id	level 1	level2	level3	level4	其他字段

表qq:

level_rank	level_id	其他字段

两个表的关联条件是: qq.level_rank = 1 时, 用qq.level_id =ci. level1 ;qq.level_rank = 2 时, 用qq.level_id =ci. level2; qq.level_rank = 3 时, 用qq.level_id =ci. level3; qq.level_rank = 4 时, 用qq.level_id =ci. level4;

1.在 mysql gp gp impala 数据库中可以如下形式join, 在on 后面写case 条件.

select *
from 
(select 111 as level_1 ,111 as level_2 ,111 as level_3,111 as level_4
union all select 122 as level_1 ,122 as level_2 ,122 as level_3,122 as level_4
union all select 333 as level_1 ,331 as level_2 ,333 as level_3,333 as level_4
union all select 111 as level_1 ,111 as level_2 ,111 as level_3,444 as level_4) ci
left join
(select 1 as level_rank,111 as level_id  
union all select 2 as level_rank ,222 as level_id 
union all select 3 as level_rank ,333 as level_id 
union all select 4 as level_rank ,44 as level_id) qq
on case qq.level_rank when 1 then qq.