解決GAT專案Bug：車裝車飾費用管理中的裝飾費統計（車輛）統計資料不對

ZHOU_VIP發表於2017-03-24

問題描述：



解決：斷點檢視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~~

相關文章