描述:待命车数量为168-7=161,而明细中加起来是168,数量不对
以前我写的SQL:
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
and (ext.process_status <3)) cc,
DICTIONARY_ITEM a
where a.dic_type = 'HOST_VEH_TYPE' and cc.HOST_VEH_TYPE = a.item_value
and ((cc.owner_appsysid, cc.owner_appdeptid) in
(select appsysid, appdeptid
from table(get_subdept(-1, 2000, 0)))) )m group by m.item_name
加起来的数量为168,是总的数量,并不是待命车的数量啊
修改SQL如下:测试待命车的数量为161,正确!
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,--车辆处理状态:1-正常;2-处置中;3-报废中;4-已处置;5-已报废
DICTIONARY_ITEM a
where a.dic_type = 'HOST_VEH_TYPE' and cc.HOST_VEH_TYPE = a.item_value
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, 2000, 0))))) tt)
and ((cc.owner_appsysid, cc.owner_appdeptid) in
(select appsysid, appdeptid
from table(get_subdept(-1, 2000, 0)))) )m group by m.item_name
因为我们发现任务车的SQL是这样的,有了灵感,在总的数量中通过hostid把任务车过滤掉,想到了这一点,其实很简单