复杂sql

这里记录一条mybatis中的sql，涉及以下几点：

• 1.foreach
• 2.大于等于号，小于号
• 3.foreach遍历
• 4.模糊匹配
• 5.and 和or 的复杂拼接
• 6.一个巧妙的用法：1=0

<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 >='${param.startTime}'
    </if>
    <if test='param.endTime!=null and param.endTime!=""'>
      and reg_time <='${param.endTime}'
    </if>
    <if test="param.payTaxStatus != null and param.payTaxStatus != ''">
      <if test="param.payTaxStatus == 1">
        and last_year_tax >= 0
      </if>
      <if test="param.payTaxStatus == 2">
        and (last_year_tax < 0 OR  ISNULL(last_year_tax))
      </if>
    </if>

    <if test='param.startEsDate!=null and param.startEsDate!=""'>
      and esdate >= '${param.startEsDate}'
    </if>
    <if test='param.endEsDate!=null and param.endEsDate!=""'>
      and esdate <= '${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 > '${param.startLastYearTax}'
    </if>
    <if test='param.endLastYearTax!=null and param.endLastYearTax!="" '>
      and lastYearTax <= '${param.endLastYearTax}'
    </if>
    ORDER BY lastYearTax desc, build_file_status desc,create_time desc
  </select>