其他sql格式也在更新中,可直接查看我的这个系列,要是没有你需要的格式,可在评论或私信我
子查询中有子查询,子子查询没有和外表a表关联
oracle 原sql
select
nvl((select power(1 + lm.rate, 1 / 365) - 1
from B lm
where lm.riskcode = a.riskcode
and lm.ratestartdate =
(select max(lr.ratestartdate)
from B lr
where lm.riskcode = lr.riskcode)
and lm.rate > 0),
0) as MinInterestRate,
from
A lj
hive 改sql
select
nvl(lding.targetcode,0) as bankcode
from
A lj
LEFT JOIN
(select max(ratestartdate) as maxtar , riskcode
from B
group by riskcode
) lam
on lam.riskcode = a.riskcode
LEFT JOIN
(select (power(1 + rate, 1 / 365) - 1) as pow,riskcode,ratestartdate
from B
where rate>0
group by riskcode,ratestartdate,rate
) lma
on lma.riskcode = a.riskcode
and lma.ratestartdate =lam.maxtar
过程:lm.riskcode = lr.riskcode这个字段都是B表的字段,他自己关联了自己,所以拆开需要找到a表和b表的关联条件,用来去重,如果你需要子子查询和a表有关联的sql示例,可以看我上上篇
链接: 上上篇的子查询嵌套子查询的sql案例.