惠安惠達sql優化：已出車後，待命車數量明細中該車最好顯示為0，使用者體驗較好

描述：執法車已經出車了，在待命車明細中看不到了，最好顯示為0，使用者體驗較好

修改sql，用左連線，結合Oracle的nvl()函式，查詢結果可以看到執法車的數量為0

select t1.item_name hostVehType,nvl(t2.hostVehTypeCount,0) hostVehTypeCount from DICTIONARY_ITEM t1 left join (
select m.item_name hostVehType, count(m.item_name) hostVehTypeCount from
(select cc.hostid,
	cc.HOST_VEH_TYPE,
	cc.owner_appsysid,
	cc.owner_appdeptid,a.item_name
from (select ext.hostid,ext.HOST_VEH_TYPE,ext.owner_appsysid,ext.owner_appdeptid
	   from position_host_info p
	   left join position_host_info_extend ext
		 on p.hostid = ext.hostid
	  where ext.verify_status = 1 --稽核狀態：0-未稽核，1-已稽核
		and (ext.process_status <3)) cc left join--車輛處理狀態：1-正常；2-處置中；3-報廢中；4-已處置；5-已報廢
	DICTIONARY_ITEM a
 on cc.HOST_VEH_TYPE = a.item_value where a.dic_type = 'HOST_VEH_TYPE'
and  cc.hostid not in--過濾任務車數量
(select tt.host_id
	  from (SELECT t.host_id
			  FROM vd_veh_assign_list t
			  LEFT JOIN position_host_info_extend ext
				ON t.host_id = ext.hostid
			 WHERE t.execute_status = 1--派車單執行狀態：0未執行；1執行中；2執行完成
			   and ext.PROCESS_STATUS < 3
			   AND ((ext.owner_appsysid, ext.owner_appdeptid) in
				   (select appsysid, appdeptid
					   from table(get_myviewdeptlist(-1, 5657, 1))))) tt
	  join position_host_info pp
		on tt.host_id = pp.hostid)      
and ((cc.owner_appsysid, cc.owner_appdeptid) in
       (select appsysid, appdeptid
         from table(get_subdept(-1, 5657, 1)))) )m  group by m.item_name         
)t2 on t1.item_name  = t2.hostVehType where t1.dic_type = 'HOST_VEH_TYPE'