<!-- 地市处理超时的工单数 -->
<select id="cityDealTimeoutList" resultType="org.iplatform.report.bean.CtOpComplainBean" parameterType="org.iplatform.report.bean.TimeCityDto">
select distinct b.*,a.operate_time - a.processing_time/86400 operate_start_time ,a.operate_time operate_end_time,trunc(a.processing_time/60) processing_time,'地市处理' processing_type
from zj_ipwf_log a, ipwf_proc_complain b
where a.processing_time > 30 * 60
and a.main_id = b.s_id
and b.is_complain = 1
and a.from_activity_id = 'CityActivity'
and a.to_activity_id in ('ConfirmActivity','DraftActivity')
and a.operate_time >= b.s_create_time
<if test="dto != null and dto.start_time != null and dto.start_time != '' ">
and <![CDATA[b.s_create_time >= TO_DATE (#{dto.start_time}, 'yyyy-mm-dd')]]>
</if>
<if test="dto != null and dto.end_time != null and dto.end_time != '' ">
and <![CDATA[b.s_create_time < TO_DATE (#{dto.end_time}, 'yyyy-mm-dd') + 1]]>
</if>
<if test="dto != null and dto.city != null and dto.city != '' and dto.city != '全省'">
and <![CDATA[b.s_create_city_name = #{dto.city}]]>
</if>
</select>
<!--查询所有地市所有的工单-->
<select id="findAllForHistorySheets" resultType="org.iplatform.larkbird.util.bean.ct.CtComplainBean">
SELECT
*
FROM
IPWF_PROC_COMPLAIN t
LEFT JOIN
(SELECT count( * ) AS is_coll, MAIN_ID, collect_type
FROM
IPWF_COMPLAIN_COLLECT
WHERE
USER_LOGIN_NAME = #{manName}
<if test='collect_type != null and collect_type != "" '>
AND (
collect_type = #{collect_type}
)
</if>
GROUP BY
MAIN_ID,collect_type
) coll ON t.s_id = coll.main_id
/* LEFT JOIN zj_ipwf_log a on a.main_id = t.s_id
and a.from_activity_id = 'CityActivity'
and a.to_activity_id in ('ConfirmActivity','DraftActivity')
and a.operate_time >= t.s_create_time*/
WHERE
is_complain = 1
<!-- aiops巡检添加 -->
<if test="keywords != null">
AND
<foreach collection="keywords" item="keyword" open="(" close=")" separator="or">
s_title Like '%' || #{keyword} || '%'
</foreach>
</if>
<if test="querytext != null and querytext != '' ">
AND (
s_title LIKE '%' || #{querytext} || '%'
OR s_running_id LIKE '%' || #{querytext} || '%'
OR s_other_system_id LIKE '%' || #{querytext} || '%'
OR accept_number LIKE '%' || #{querytext} || '%'
)
</if>
<if test="state != null and state != '' ">
AND (
ct_state LIKE '%' || #{state} || '%'
)
</if>
<if test="running_id != null and running_id != '' ">
AND (
s_running_id LIKE '%' || #{running_id} || '%'
OR s_other_system_id LIKE '%' || #{running_id} || '%'
)
</if>
<if test="title != null and title != '' ">
AND (
s_title LIKE '%' || #{title} || '%'
)
</if>
<if test="city_name != null and city_name != '' ">
AND (
s_create_city_name LIKE '%' || #{city_name} || '%'
)
</if>
<if test="accept_number != null and accept_number != '' ">
AND (
accept_number LIKE '%' || #{accept_number} || '%'
)
</if>
<if test="is_reopen != null and is_reopen != '' ">
AND (
is_reopen = #{is_reopen}
)
</if>
<if test="is_timeout != null and is_timeout != '' ">
AND (
is_timeout = #{is_timeout}
-- or a.processing_time > 30 * 60
)
</if>
<if test="create_man_name != null and create_man_name != '' ">
AND (
s_create_man_name LIKE '%' || #{create_man_name} || '%'
)
</if>
<if test="pre_man != null and pre_man != '' ">
AND (
s_pre_man_name LIKE '%' || #{pre_man} || '%'
OR s_pre_man_id LIKE '%' || #{pre_man} || '%'
)
</if>
<if test="solver_name != null and solver_name != '' ">
AND (
solver_name LIKE '%' || #{solver_name} || '%'
)
</if>
<if test="reject_type != null">
AND t.S_ID
<if test="reject_type == 'no_reject'">
NOT
</if>
IN (
SELECT
a.MAIN_ID
FROM
IPWF_LOG a
<where>
<if test="reject_type == 'pr_reject'">
a.from_activity_id = 'ProvinceActivity' AND a.to_activity_id = 'FinishActivity'
</if>
<if test="reject_type == 'city_reject'">
a.from_activity_id = 'CityActivity' AND a.to_activity_id = 'DraftActivity'
</if>
<if test="reject_type == 'all_reject'">
((a.from_activity_id = 'ProvinceActivity' AND a.to_activity_id = 'FinishActivity')
OR (a.from_activity_id = 'CityActivity' AND a.to_activity_id = 'DraftActivity'))
</if>
<if test="reject_type == 'no_reject'">
((a.from_activity_id = 'ProvinceActivity' AND a.to_activity_id = 'FinishActivity')
OR (a.from_activity_id = 'CityActivity' AND a.to_activity_id = 'DraftActivity'))
</if>
<if test="start != null and start != '' ">
AND <![CDATA[a.operate_time >= TO_DATE (#{start}, 'yyyy-mm-dd hh24:mi:ss')]]>
</if>
<if test="end != null and end != '' ">
AND <![CDATA[a.operate_time <= TO_DATE (#{end}, 'yyyy-mm-dd hh24:mi:ss')]]>
</if>
</where>
GROUP BY
a.MAIN_ID
)
</if>
<if test='is_province != null and is_province == "1" '>
AND (
s_other_system_id is not null
)
</if>
<if test='is_province != null and is_province == "0" '>
AND (
s_other_system_id is null
)
</if>
<if test="start != null and start != '' ">
AND <![CDATA[s_create_time >= TO_DATE (#{start}, 'yyyy-mm-dd hh24:mi:ss')]]>
</if>
<if test="end != null and end != '' ">
AND <![CDATA[s_create_time <= TO_DATE (#{end}, 'yyyy-mm-dd hh24:mi:ss')]]>
</if>
ORDER BY t.s_lastedit_time DESC
</select>
如上图，在做查询所有地市工单的时候（第二段代码），少了一个地市处理超时的条件（第一段代码），导致查出来的超时工单数据不全，现使用leftjoin将第一段代码中的另一张表连接起来，第一段代码中的条件该怎么写才能加到第二段代码中呢？
