mybatis提取<where><if>共用代码

本文介绍了一种在MyBatis项目中减少DAO层SQL语句重复的方法,通过将常用的查询条件封装到公共模块中,提高代码复用性和可维护性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  mybatis项目dao层中很多sql语句都会拥有某些相同的查询条件,以<where><if test=""></if></where>的形式拼接在sql语句后,一个两个的sql语句感觉不到什么,但是如果查询语句特别多,但是查询的条件总是类似的,那就可以考虑把<where><if>这部分代码抽取出来,封装一下,然后需要条件搜索的sql语句直接引用就可以了。

  先来看下没有抽取代码之前的条件sql语句

复制代码
第一条
<select id = "getUserEmailByProvinceAndOrderType" resultType="String">
        select DISTINCT(wo_responsibility) from t_view_workorder
        <where>
            <if test="province != '全国' and province != null">
                wo_province = #{province}
            </if>
            <if test="orderType != '全部' and orderType != null">
                and wo_type = #{orderType}
            </if>
            <if test="email != ''">
                and wo_responsibility = #{email}
            </if>
        </where>
</select>
第二条
<select id = "getUndoneDelayOrderByProvinceAndOrderTypeAndUserEmail" resultType="com.chinamobile.sias.workorder.po.Workorder">
        select * from t_view_workorder
        <where>
        <if test="province != '全国' and province != null">
                wo_province = #{province}
            </if>
            <if test="orderType != '全部' and orderType != null">
                and wo_type = #{orderType}
            </if>
            <if test="email != ''">
                and wo_responsibility = #{email}
            </if>
        <if test="true"> and (wo_complete_time is null or wo_complete_time='') and (select curdate()) >= wo_regulations_time </if>

    </where>

</select>
复制代码

以上是两条sql语句,可以看出,两个sql语句中有某些查询条件是相同的

复制代码
        <if test="province != '全国' and province != null">
            wo_province = #{province}
        </if>
        <if test="orderType != '全部' and orderType != null">
            and wo_type = #{orderType}
        </if>
        <if test="email != ''">
            and wo_responsibility = #{email}
        </if>    
复制代码

此时我们就可以对此段判断条件进行提取。如下:

复制代码
<sql id="common_where_if">
        <if test="province != '全国' and province != null">
            wo_province = #{province}
        </if>
        <if test="orderType != '全部' and orderType != null">
            and wo_type = #{orderType}
        </if>
        <if test="email != ''">
            and wo_responsibility = #{email}
        </if>
</sql>
复制代码

此时把<where>标签下相同的判断条件提去了出来,id自己取,这里定为 common_where_if.

那么如何使用这段代码呢,如下:

<include refid="common_where_if"/>

格式如下:

复制代码
<select id = "getUserEmailByProvinceAndOrderType" resultType="String">
        select DISTINCT(wo_responsibility) from t_view_workorder
        <where>
            <include refid="common_where_if"/>
        </where>

</select>
复制代码

此时就在<where>标签中引用了共用的判断条件,再多的sql语句,再多的查询条件,只需要一个<include>就能解决重复的代码。

select <if test="ygdm != null"> HLXJLIST.ZLHS as ygdm,(select ygxm from EMPLIST where EMPLIST.YGDM = HLXJLIST.ZLHS) as ygxm, </if> count(1) as txfscs, hemolist.TXFS,(select txfsmc from txfsdict where txfsdict.txfs = hemolist.txfs) as txfsmc from HLXJLIST,hemolist,AREADEV where HEMOLIST.SBID = AREADEV.SBID and HLXJLIST.Xtxh = hemolist.xtxh <if test="ygdm != null"> and HLXJLIST.ZLHS = #{ygdm} </if> AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} and HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> <if test="ygdm != null"> group by ZLHS,txfs </if> <if test="ygdm == null"> group by txfs </if> order by txfs </select> <select id="getSingleTxgzl" parameterType="java.util.Map" resultMap="txgzlReport"> SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 1 as CCZL, '穿刺' as CCZLMC, 'cchs' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, VASLIST, TLLXDICT, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.CCHS = EMPLIST.YGDM AND HEMOLIST.TLXH = VASLIST.TLXH AND VASLIST.TLLX = TLLXDICT.JLID AND HEMOLIST.SBID = AREADEV.SBID AND TLLXDICT.TLTYPE = 1 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 2 as CCZL, '治疗' as CCZLMC, 'zlhs' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 3 as CCZL, '导管' as CCZLMC, 'dgrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, VASLIST, TLLXDICT, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.CCHS = EMPLIST.YGDM AND HEMOLIST.TLXH = VASLIST.TLXH AND VASLIST.TLLX = TLLXDICT.JLID AND HEMOLIST.SBID = AREADEV.SBID AND TLLXDICT.TLTYPE = 2 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 4 as CCZL, '导管溶栓' as CCZLMC, 'dgrsrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, HLJLLIST, HLJLLIST_QTSJ, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.XTXH = HLJLLIST.XTXH AND HLJLLIST.JLID = HLJLLIST_QTSJ.JLID AND HEMOLIST.SBID = AREADEV.SBID AND HLJLLIST_QTSJ.QTSJID = 9 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 5 as CCZL, '新内瘘穿刺' as CCZLMC, 'xnlccrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, HLJLLIST, HLJLLIST_QTSJ, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.XTXH = HLJLLIST.XTXH AND HLJLLIST.JLID = HLJLLIST_QTSJ.JLID AND HEMOLIST.SBID = AREADEV.SBID AND HLJLLIST_QTSJ.QTSJID = 10 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 6 as CCZL, '第一班' as CCZLMC, 'zblb1rc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.ZBLB = 1 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 7 as CCZL, '第二班' as CCZLMC, 'zblb2rc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.ZBLB = 2 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 8 as CCZL, '第三班' as CCZLMC, 'zblb3rc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.ZBLB = 3 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 9 as CCZL, '第四班' as CCZLMC, 'zblb4rc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.ZBLB = 4 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 10 as CCZL, '无肝素' as CCZLMC, 'wgsrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.KYFA = 3 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 11 as CCZL, '住院' as CCZLMC, 'zyrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.BRLY = 2 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 12 as CCZL, '人造血管' as CCZLMC, 'rzxgrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV, VASLIST, TLLXDICT WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.TLXH = VASLIST.TLXH AND VASLIST.TLLX = TLLXDICT.JLID AND (TLLXDICT.LXMC = '人造血管' or VASLIST.TLBW IN (SELECT DMSB FROM DICTLIST WHERE DMLB = 44 AND DMMC = '人造血管')) AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 13 as CCZL, '血液透析' as CCZLMC, 'hdrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV, TXFSDICT WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.TXFS = TXFSDICT.TXFS AND TXFSDICT.TXFSMC = 'HD' AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 14 as CCZL, '滤过' as CCZLMC, 'hdfrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV, TXFSDICT WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.TXFS = TXFSDICT.TXFS AND TXFSDICT.TXFSMC = 'HDF' AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 15 as CCZL, '灌流' as CCZLMC, 'hprc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV, TXFSDICT WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.TXFS = TXFSDICT.TXFS AND TXFSDICT.GLBZ = 1 AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL SELECT EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 16 as CCZL, 'UFHD' as CCZLMC, 'hfhd' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST, HEMOLIST, EMPLIST, AREADEV, TXFSDICT WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.TXFS = TXFSDICT.TXFS AND TXFSDICT.TXFSMC = 'UFHD' AND EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> UNION ALL select EMPLIST.YGDM, EMPLIST.YGXM, HEMOLIST.XTXH, 18 as CCZL, '透析不满3.5h人次' as CCZLMC, 'txbmrc' as pydm, <if test="tjjd == 1"> to_char(HEMOLIST.JZSJ, 'yyyy"年"') as PROJECTDATE </if> <if test="tjjd == 2"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"') as PROJECTDATE </if> <if test="tjjd == 3"> to_char(HEMOLIST.JZSJ,'yyyy"年"mm"月"dd"日"') as PROJECTDATE </if> <if test="tjjd == 4"> to_char(HEMOLIST.JZSJ,'yyyy"年"q"季度"') as PROJECTDATE </if> FROM HLXJLIST,HEMOLIST,EMPLIST,AREADEV WHERE HLXJLIST.XTXH = HEMOLIST.XTXH AND HLXJLIST.ZLHS = EMPLIST.YGDM AND HEMOLIST.SBID = AREADEV.SBID AND HEMOLIST.JZSJ >= #{begin,jdbcType=TIMESTAMP} AND HEMOLIST.JZSJ <= #{end,jdbcType=TIMESTAMP} AND HEMOLIST.TXZT = 3 and EMPLIST.YGDM = #{ygdm,jdbcType=VARCHAR} and hlxjlist.ZLSJ < 3.5 <if test="centerid gt 0"> and HEMOLIST.CENTERID = #{centerid,jdbcType=DECIMAL} </if> <if test="qyid != null and qyid != ''"> and AREADEV.qyid IN (${qyid}) </if> <if test="whereClause != null and whereClause != ''"> ${whereClause} </if> ORDER BY PROJECTDATE 帮我优化一下这个sql
07-10
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值