其他sql格式也在更新中,可直接查看这个系列,要是没有你需要的格式,可在评论或私信我
目录
其他sql格式也在更新中,可直接查看这个系列,要是没有你需要的格式,可在评论或私信我oracle 原sql
select (select nvl(sum(f.money), 0)from F fwhere f.moneytype = 'GL'and f.busytype in ('YT', 'XT', 'PG', 'PV', 'PD')and f.contno = a.contnoand f.insuaccno = a.insuaccnoand f.polno = a.polnoand f.feecode in (select p.feecodefrom lmriskfee pwhere p.insuaccno = f.insuaccnoand p.feekind = '03'and p.feetakeplace in ('09', '10')union allselect '000000' from dual)) +(select nvl(abs(sum(lc.fee)), 0)from LC lcwhere lc.contno = a.contnoand lc.insuaccno = a.insuaccnoand lc.polno = a.polnoand lc.feecode in(select p.feecodefrom P pwhere 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 aleft join (selectnvl(sum(f.money),0) as money,f.contno,f.insuaccno,f.polnofromF fwheref.moneytype = 'GL'and f.busytype in ('YT', 'XT', 'PG', 'PV', 'PD')and f.feecode in (selectp.feecodefromP pinner join F f onp.insuaccno = f.insuaccnowherep.feekind = '03'and p.feetakeplace in ('09', '10')union allselect'000000')group byf.contno,f.insuaccno,f.polno) b onb.contno = a.contnoand b.insuaccno = a.insuaccnoand b.polno = a.polnoleft join (selectnvl(sum(f.fee),0) as fee,f.contno,f.insuaccno,f.polnofromF fwheref.feecode in (selectp.feecodefromP pwherep.feekind = '03'and p.feetakeplace = '01'and p.feetakeplace = '01'union allselect'000000')group byf.contno,f.insuaccno,f.polno) c onc.contno = a.contnoand c.insuaccno = a.insuaccnoand c.polno = a.polno
注:可当练习(~。 ~)
oracle sql 转换成 hive sql -三重子查询union all+运算符改为关联查询中嵌套子查询-子查询转关联查询(十一)