问题描述:
解决:断点查看sql语句,并加上参数执行
发现sql语句统计的不对,nvl2(t2.times, 1, 0),意思是有值为1没值为0
下面的半句已经可以累积次数了,不需要再nvl2(t2.times, 1, 0) as times
所以修改SQL,直接t2.times
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
测试OK~~
----------------------------------------------------------------------------------------------------------------------
补充说明:参考停车管理里面的车辆统计,
select t1.hostno,
nvl(t2.parkTimes, 0) as parkTimes,
nvl(t2.totalParkFee, 0) as totalParkFee
from (select p.hostno, p.hostid
from position_host_info p
where p.status = 1
and (exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) f
where p.APPSYSID = f.appsysid
and p.APPDEPTID = f.appdeptid))) t1
left join (select cpf.host_id,
cpf.HOST_NO,
to_char(count(*)) as parkTimes,
to_char(sum(cpf.PARK_FEE)) as totalParkFee
from COST_PARK_FEE cpf
where (exists (select appsysid, appdeptid
from table(get_subdept(8196, 23551, 3)) f
where cpf.APPSYSID = f.appsysid
and cpf.APPDEPTID = f.appdeptid))
and cpf.PAY_DATE >= to_date('2017-03-20', 'yyyy-mm-dd')
and cpf.PAY_DATE <= to_date('2017-03-31', 'yyyy-mm-dd')
and cpf.fee_verify = 1
group by cpf.host_id, cpf.HOST_NO) t2
on t1.hostid = t2.host_id
所以上面的装饰费统计(车辆)sql改为:
select t1.hostno, nvl(t2.times,0) as 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
代码中改成:
测试OK~~