惠安惠达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'