NVL函数的用法:
NVL(expr1,expr2),表示如果expr1的值为NULL时,则返回expr2的值,否则返回expr1的值。
NVL2函数的用法:NVL2(expr1,expr2,expr3),检查表达式expr1,表示如果expr1不为NULL时,返回值为expr2,否则返回值为expr3。
select t1.hostno, t2.times, nvl(t2.cost, 0) as cost
from (select p.hostid, p.hostno
from position_host_info p
left join position_host_info_extend e
on p.hostid = e.hostid
where e.verify_status = 1
and e.process_status <> 4
and e.process_status <> 5
and p.appsysid = 23551
and p.appdeptid = 3) t1
left join
(select p.hostno as hostNo,
t.host_id,
sum(nvl2(t.DECORATE_COST, 1, 0)) as times,
sum(nvl(t.DECORATE_COST, 0)) as cost
from position_host_info p
left join position_host_info_extend e
on p.hostid = e.hostid
left join COST_DECORATE t
on p.hostid = t.host_id
and t.PAYMENT_DATE >= to_date('2017-03-20', 'yyyy-mm-dd')
and t.PAYMENT_DATE <= to_date('2017-03-24', 'yyyy-mm-dd')
where p.appsysid = 23551
and p.appdeptid = 3
and e.verify_status = 1
and e.process_status <> 4
and e.process_status <> 5
and t.apply_status = 11
group by p.hostno, t.host_id) t2
on t1.hostid = t2.host_id