其他sql格式也在更新中,可直接查看这个系列,要是没有你需要的格式,可在评论或私信我
个人目录
hive的nvl中的子查询
round和power函数理论
round | select round(123.4567,2) = 123.46 | 进行四舍五入处理,保留2位小数 |
---|---|---|
power | select power(1,365) | 返回以 1 的 365 次幂 |
子查询中有2个表外字段关联写法sql案例
oracle 原sql
SELECT
(SELECT
round((
SELECT
power(
select rate
from aaa
where code = b.code
and val = 'D'
and vip = l.Vip)+ 1, 365)-1)* 100,
2)
FROM
dual) as aaa
FROM
bbb b,
lll l
WHERE
b.polno = l.polno
hive 改sql
SELECT
round((power((a.rate + 1), 365)-1)* 100,
2) as Interestrate
FROM
bbb b,
lll l
left join aaa a
on a.code = b.code
and a.val = 'D'
and a.vip = l.Vip
WHERE
b.polno = l.polno
想法:子查询中关联了两张表,直接取出来合并到整个大sql里面
格外笔记
一开始我以为power和round这个是像sum这种聚合函数,需要分组才能通过演算
SELECT
b.polno as polno,
a.code as code,
round((power((a.rate + 1), 365)-1)* 100,
2) as Interestrate
FROM
bbb b,
lll l
left join aaa a
on a.code = b.code
and a.val = 'D'
and a.vip = l.Vip
WHERE
b.polno = l.polno