weixin_53942559 2021-06-26 10:57 采纳率: 70.8%
浏览 21
已结题

两段sql怎么整合?

<!-- 地市处理超时的工单数 --> 
<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将第一段代码中的另一张表连接起来,第一段代码中的条件该怎么写才能加到第二段代码中呢?

 

  • 写回答

1条回答 默认 最新

  • CSDN专家-sinJack 2021-06-26 11:00
    关注

    这两段sql查询的字段都一样吗。如果不好整,你加个标识区分一下就好了。

    <if test="">判断一下,然后分别执行不同的where条件。

    如有帮助,请采纳。

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 系统已结题 9月20日
  • 已采纳回答 9月12日

悬赏问题

  • ¥15 怎么实现不管输入多少字符或者数组或者空格 输出的时候先显示输入的字符数组或者空格再提示输出。输出的结果每个字符后面都带上一个空格
  • ¥15 ros小车启动launch文件报错
  • ¥15 vs2015到期想登陆但是登陆不上
  • ¥20 Intellij实现web登录界面
  • ¥15 IPQ5018制作烧录固件,boot运行失败(操作系统-linux)(相关搜索:操作系统)(相关搜索:操作系统)
  • ¥20 icefall在librispeech基础上加入个人数据集
  • ¥30 keepalive高可用故障运维配置询问
  • ¥15 求帮助!国家电网内网u盘突然识别不出来了。
  • ¥15 matlab语音变速变调同时实现
  • ¥15 如何用Thoony写ESP32温湿度检测无源蜂鸣器报警代码?