前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >复杂sql

复杂sql

作者头像
IT云清
发布2019-01-22 10:59:24
5040
发布2019-01-22 10:59:24
举报
文章被收录于专栏:IT云清
这里记录一条mybatis中的sql,涉及以下几点:
  • 1.foreach
  • 2.大于等于号,小于号
  • 3.foreach遍历
  • 4.模糊匹配
  • 5.and 和or 的复杂拼接
  • 6.一个巧妙的用法:1=0
代码语言:javascript
复制
 <select id="findByPageForEntFile" resultMap="resMap2" statementType="STATEMENT">
    select <include refid="Base_Column_List2"/> from ent_file e where 1=1

    <if test="param.taxFromArea != null">
      and tax_from_area = '${param.taxFromArea}'
    </if>
    <if test='param.labelImpType != null and param.labelImpType!=""' >
      <if test="param.labelImpType == 'label_high_legal_risk'">
        and  ${param.labelImpType} >= 5
      </if>
      <if test="param.labelImpType != 'label_high_legal_risk'">
        and  ${param.labelImpType} >= 1
      </if>
    </if>

    <if test='param.zoneId!=null and param.zoneId!="" and param.zoneId!="-1"'>
      and zone_id='${param.zoneId}'
    </if>
    <if test='param.startTime!=null and param.startTime!=""'>
      and reg_time &gt;='${param.startTime}'
    </if>
    <if test='param.endTime!=null and param.endTime!=""'>
      and reg_time &lt;='${param.endTime}'
    </if>
    <if test="param.payTaxStatus != null and param.payTaxStatus != ''">
      <if test="param.payTaxStatus == 1">
        and last_year_tax &gt;= 0
      </if>
      <if test="param.payTaxStatus == 2">
        and (last_year_tax &lt; 0 OR  ISNULL(last_year_tax))
      </if>
    </if>

    <if test='param.startEsDate!=null and param.startEsDate!=""'>
      and esdate &gt;= '${param.startEsDate}'
    </if>
    <if test='param.endEsDate!=null and param.endEsDate!=""'>
      and esdate &lt;= '${param.endEsDate}'
    </if>

    <if test='param.keyWord!=null and param.keyWord!=""'>
      and (ent_name like concat('%','${param.keyWord}','%')
      or frname like concat('%','${param.keyWord}','%')
      or address like concat('%','${param.keyWord}','%')
      )
    </if>
    and delete_flag=0

    <if test="param.moveStatusList != null">
      and (move_status in
      <foreach collection="param.moveStatusList" index="index" item="item" open="(" separator="," close=")">
        '${item}'
      </foreach>
      <if test="param.typeCode2 == 1">
        or move_status = NULL
      </if>)
    </if>
    <if test="param.entStatusList != null">
      and ent_status in
      <foreach collection="param.entStatusList" index="index" item="item" open="(" separator="," close=")" >
        '${item}'
      </foreach>
    </if>
    <if test="param.emergentCodeList != null">
      and e.eid in
      <foreach collection="param.emergentCodeList" index="index" item="item" open="(" separator="," close=")" >
        '${item}'
      </foreach>
    </if>

    <if test="param.typeCode == 1">
      and
      ( 1 = 0

      <if test="param.labelTaxpayers != null" >
        OR label_taxpayers = '${param.labelTaxpayers}'
      </if>
      <if test="param.labelListed != null" >
        OR label_listed = '${param.labelListed}'
      </if>
      <if test="param.labelHighOperatingRisk != null" >
        OR label_high_operating_risk = '${param.labelHighOperatingRisk}'
      </if>
      <if test="param.labelDishonesty != null" >
        OR label_dishonesty = '${param.labelDishonesty}'
      </if>


      <if test='param.majorProjectStatus!=null and param.majorProjectStatus!=""'>
        OR major_project_status= '${param.majorProjectStatus}'
      </if>

      <if test="param.labelCreditBaseList != null">
        OR label_credit_base in
        <foreach collection="param.labelCreditBaseList" index="index" item="item" open="(" separator="," close=")" >
          ${item}
        </foreach>
      </if>

      <if test="param.labelHighnewTechList != null">
        OR label_highnew_tech in
        <foreach collection="param.labelHighnewTechList" index="index" item="item" open="(" separator="," close=")" >
          ${item}
        </foreach>
      </if>

      <if test="param.entFinanceRiskList != null">
        OR e.eid in
        <foreach collection="param.entFinanceRiskList" index="index" item="item" open="(" separator="," close=")" >
          '${item}'
        </foreach>
      </if>

      )
    </if>

    <if test="param.taxEidList != null">
      and e.eid in
      <foreach collection="param.taxEidList" index="index" item="item" open="(" separator="," close=")">
        '${item}'
      </foreach>
    </if>

    HAVING 1 = 1
    <if test='param.startLastYearTax!=null and param.startLastYearTax!="" '>
      and lastYearTax &gt; '${param.startLastYearTax}'
    </if>
    <if test='param.endLastYearTax!=null and param.endLastYearTax!="" '>
      and lastYearTax &lt;= '${param.endLastYearTax}'
    </if>
    ORDER BY lastYearTax desc, build_file_status desc,create_time desc
  </select>
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2018年04月27日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 这里记录一条mybatis中的sql,涉及以下几点:
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档