复杂sql语句 组合判断other choose where and if

// 处理模糊查询 百分号问题
 <select id="getRolePage" resultType="com.tiansu.hlms.toc.bean.role.vo.RoleVoList">
    select id as id,role_name as roleName,role_code as roleCode,description,tenant_id as tenantId from hlms_toc_role where disabled=0
    <if test="keyword!=null and keyword!=''">
      and (role_name like CONCAT('%',concat('/',#{keyword},'%')) ESCAPE '/' or
      role_code like CONCAT('%',concat('/',#{keyword},'%')) ESCAPE '/')
    </if>
  </select>
-------
 <select id="selectByDocPatFollow" resultMap="BaseResultMap">
    select a.id,cn_name,a.latest_time,a.follow_count,a.icon,a.flowup_cycle,a.sex,a.is_overTime,a.mobile
    from jf_sys_patient a
    left join jf_doctor_pat b on a.id=b.pat_id where b.doctor_id=#{docId} and a.follow_count !=0
    <if test="name != null and name != ''">
      and cn_name like  concat(#{name},'%')
    </if>
    <choose>
      <when test='num=="2"'>order by follow_count desc</when>
      <when test='num=="1"'>order by follow_count asc</when>
    </choose>
    <choose>
      <when test="date=='1'.toString()">order by latest_time desc</when>
      <when test="date=='2'.toString()">order by latest_time asc</when>
    </choose>
  </select>

去掉第一个where
 select id, tenant_id, title, subtitle, `type`, type_details, reporter, report_time, template_id, document_id,
        document_type, reporter_name, document_path, DATE_FORMAT(report_time, '%Y-%m-%d') AS document_date
        from hlms_report_document
        <where>
        <if test="tenantIdList!=null and tenantIdList.size()&gt; 0">
            and tenant_id in
            <foreach item="item" index="index" collection="tenantIdList" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="keyword != null and keyword != ''">
            and subtitle LIKE CONCAT('%',replace_special_str_fun(#{keyword}),'%') ESCAPE '/'
        </if>
        <if test="startDate != null and startDate != '' ">
            and date_format(report_time,'%Y-%m-%d') &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            and date_format(report_time,'%Y-%m-%d') &lt;= #{endDate}
        </if>
        </where>
        order by report_time desc

<select id="getPatientListTwo" resultType="com.jianfan.mdt.patient.vo.PatListVo">
    select distinct a.id as id,a.cn_name as cnName,a.mobile as
    mobile,a.source as source,a.sex as sex,a.date_birth as dateBirth,
    a.icon as icon,a.im_id as imId,a.create_time as createTime, a.dis_host as disHost,
    a.mdt_status as mdtStatus,a.mdt_count as mdtCount,host_office as hostOffice,host_id as hostId,'${readWrite}' as operAuth
    from jf_sys_patient a
    <choose>
      <when test="typeId=='2'.toString()">
       <choose>
         <when test='source=="1"'>
           left join jf_doctor_pat b on a.id=b.pat_id where b.doctor_id=#{docId} and a.create_user=#{docId}
           <if test="hostId!=null and hostId!=''">
             and a.host_id=#{hostId}
           </if>
           <include refid="patListTwo"/>
           and a.is_use=1
         </when>
         <otherwise>
           where  a.host_id=#{hostId}
            <choose>
              <when test='source=="4"'>
                and source='1'
              </when>
              <otherwise>
                <if test="source!=null and source!='' and source!='0'.toString()">
                  and  a.source=#{source}
                </if>
            </otherwise>
            </choose>
           <include refid="patListTwo"/>
           and a.is_use=1
         </otherwise>
       </choose>
      </when>
      <when test="typeId=='3'.toString()">
        left join jf_doctor_pat b on a.id=b.pat_id where b.doctor_id=#{docId}
        <if test="source=='1'.toString()">
          and a.create_user=#{docId}
        </if>
        <include refid="patListTwo"/>
        and a.is_use=1
      </when>
      <when test="typeId=='4'.toString()">
        <choose>
          <when test="source=='1'.toString()">
            left join jf_doctor_pat b on a.id=b.pat_id where b.doctor_id=#{docId} and a.create_user=#{docId}
            <include refid="patListTwo"/>
            and a.is_use=1
          </when>
          <otherwise>
            where a.host_office=#{hostOffice}
            <if test="source!=null and source!='' and source!='0'.toString()">
              and  a.source=#{source}
            </if>
            <include refid="patListTwo"/>
            and a.is_use=1
          </otherwise>
        </choose>
      </when>
      <otherwise>
       <choose>
         <when test="source=='1'.toString()">
           left join jf_doctor_pat b on a.id=b.pat_id where b.doctor_id=#{docId} and a.create_user=#{docId}
           <if test="hostId!=null and hostId!=''">
             and a.host_id=#{hostId}
           </if>
           <include refid="patListTwo"/>
           and is_use=1
         </when>
         <otherwise>
           <trim prefix="where"  prefixOverrides="and">
             <if test="hostId!=null and hostId!=''">
               and a.host_id=#{hostId}
             </if>
             <choose>
               <when test="source=='4'.toString()">
                 and source='1'
               </when>
               <otherwise>
                 <if test="source!=null and source!='' and source!='0'.toString()">
                   and  a.source=#{source}
                 </if>
               </otherwise>
             </choose>
            <include refid="patListTwo"/>
             and is_use=1
           </trim>
         </otherwise>
       </choose>
      </otherwise>
    </choose>
    order by create_time desc

  </select>

msql 在delete from 表名 如果给表起了表明 需要在 delete 后面加上别名的名称 如 delete a from test as a