with tmp as
(select * from person p left
join dic d on p.sex = d.key and d.type = 'sex'
union all
select * from person p
left join dic d on p.sex = d.key and d.type = 'job')
select
max( case when tmp.job = tmp.key and tmp.type = 'job'
then tmp.val else 0 end ) as job,
max( case when tmp.sex = tmp.key and tmp.type = 'sex'
then tmp.val else 0 end ) as sex,
id
from tmp group by id;
HiveSql-join多次相同的字典表-优化
最新推荐文章于 2023-05-16 21:53:36 发布
这个SQL查询示例展示了如何使用LEFT JOIN结合UNION ALL从person表中获取性别和职业的最大值。查询首先按ID分组,然后通过CASE语句确定每个ID的最大job和sex值。这涉及到数据的组合和聚合操作。
摘要由CSDN通过智能技术生成