oracle sql 转换成 hive sql -子查询转关联查询(十六)，hive的nvl中的子查询

其他sql格式也在更新中，可直接查看这个系列，要是没有你需要的格式，可在评论或私信我

hive的nvl中的子查询

oracle 原sql

select
 nvl(
	(select b.code
	from B b
	where b.codetype = '123'
	and b.code = a.code),
		'000000') as code
from
	A a
1
2
3
4
5
6
7
8
9

hive 改sql

select
 nvl(b.code,'000000') as code
from
	A a
	left join B b 
	on	b.codetype = '123'
	and b.code = a.code
1
2
3
4
5
6
7

hive的nvl中的双重子查询

oracle 原sql

select
 nvl(
	(select b.code
	from B b
	where b.codetype = '123'
	and b.code = a.code),
	(select c.code
	from C c
	where c.codetype = '123'
	and c.code = a.code)) as code
from
	A a
1
2
3
4
5
6
7
8
9
10
11
12

hive 改sql

select
 nvl(b.code,c.code) as code
from
	A a
	left join B b 
	on	b.codetype = '123'
	and b.code = a.code
	left join C c 
	on	c.codetype = '123'
	and c.code = a.code
1
2
3
4
5
6
7
8
9
10