oracle sql 转换成 hive sql -三重子查询union all+运算符改为关联查询中嵌套子查询-子查询转关联查询(十一)

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

oracle 原sql

select 
	(select nvl(sum(f.money), 0)
	     from F f
	      where f.moneytype = 'GL'
	        and f.busytype in ('YT', 'XT', 'PG', 'PV', 'PD')
	        and f.contno = a.contno
	        and f.insuaccno = a.insuaccno
	        and f.polno = a.polno
	        and f.feecode in (select p.feecode
	                            from lmriskfee p
	                           where p.insuaccno = f.insuaccno
	                             and p.feekind = '03'
	                             and p.feetakeplace in ('09', '10')
	                          union all
	                          select '000000' from dual)) +
	    (select nvl(abs(sum(lc.fee)), 0)
	       from LC lc
	      where lc.contno = a.contno
	        and lc.insuaccno = a.insuaccno
	        and lc.polno = a.polno
	        and lc.feecode in
	            (select p.feecode
	               from P p
	              where p.feekind = '03'
	                and p.feeitemtype = '01'
	                and p.feetakeplace = '01')) as Acc,
from 
	A a

hive 改sql

select 
  nvl(b.money,
       0)+ nvl(c.fee,
       0)
from 
	A a
	left join (
        select
                nvl(sum(f.money),
                0) as money,
                f.contno,
                f.insuaccno,
                f.polno
        from
                F f
        where
                f.moneytype = 'GL'
                and f.busytype in ('YT', 'XT', 'PG', 'PV', 'PD')
                and f.feecode in (
                select
                        p.feecode
                from
                       P p
                inner join F f on
                        p.insuaccno = f.insuaccno
                where
                        p.feekind = '03'
                        and p.feetakeplace in ('09', '10')
        union all
                select
                        '000000')
        group by
                f.contno,
                f.insuaccno,
                f.polno) b on
        b.contno = a.contno
        and b.insuaccno = a.insuaccno
        and b.polno = a.polno
left join (
        select
                nvl(sum(f.fee),
                0) as fee,
                f.contno,
                f.insuaccno,
                f.polno
        from
                F f
        where
                f.feecode in (
                select
                        p.feecode
                from
                        P p
                where
                        p.feekind = '03'
                        and p.feetakeplace = '01'
                        and p.feetakeplace = '01'
        union all
                select
                        '000000')
        group by
                f.contno,
                f.insuaccno,
                f.polno) c on
        c.contno = a.contno
        and c.insuaccno = a.insuaccno
        and c.polno = a.polno
	

注：可当练习（~。 ~）