oracle sql 转换成 hive sql -子查询转关联查询(十五).4层子查询，包含rownum（截取）,max,order desc倒序排序，case 判断

• • sql示例
  • • oracle 原sql
    • hive 改sql(hive可运行,spark可运行）

select 
		(
	select
		(case
			when is_vat = '1' then
	             (
			select
				*
			from
				(
				select
					vat_rate
				from
					C
				where
					manager_com = '8601'
				order by
					end_date desc) t1
			where
				rownum = '1')
			else
	             0
		end)
	from
		B
	where
		risk_code = a.riskcode
		and end_date in (
		select
			max(end_date)
		from
			B
		where
			risk_code = a.riskcode)) as TaxRate
from 
		A a
	

spark用逗号隔开会报笛卡尔什么东西，需要改成cross join （交叉连接）

select
	(case
			when 
				b.is_vat = '1' 
			then 
				cc.vat
		else 0
	end) as TaxRate
from 
		A a
left join (
	select
		max(end_date) as maxtar,
		risk_code
	from
		B
	group by
		risk_code ) bb
	on
	bb.risk_code = a.riskcode
left join 
	B b
	on
	b.risk_code = a.riskcode
	and b.end_date = bb.maxtar
cross join (
	select
		(row_number() over (
	order by
		c.enda desc)) as num ,
		c.vat as vat
	from
		(
		select
			vat_rate as vat ,
			end_date as enda
		from
			C
		where
			manager_com = '8601'
		order by
			enda )c ) cc on
	cc.num = 1