sql的问题，请大神们帮忙啊

            上面这张图如果前台给我传一个status=4的一个状态 我要把状态是4,5,7的数据返回去，这个sql应该怎么写 应该where后面的把
            补充下问题啊 如果传1返回1的数据  传2返回2 的数据就是传4时不同
            sql里的if判断应该怎么判断  后面还有好多判断条件的


            select d.transferID,d.PatientName,d.Mobile,d.CreateDate,d.doctorName_from,d.doctorId_from,
         d.Status,
         d.doctorId_to,d.doctorName_to,d.HospitalId_to,d.HospitalName_to
         from his_transfertreatmentrecord d 
           where 1=1
            <if test="status == '4'">
                        and Status in ('4','5','7')
            </if>
            <!-- <if test="status!=null and status!='' and status!='4'">
                        and Status = #{status,jdbcType=VARCHAR}
            </if> -->
            <if test="doctorNameFrom!=null and doctorNameFrom!=''">
                        and doctorName_from like CONCAT('%',#{doctorNameFrom,jdbcType=VARCHAR},'%')
            </if>
            <if test="patientName!=null and patientName!=''">
                        and patientName like CONCAT('%',#{patientName,jdbcType=VARCHAR},'%')
            </if>

            <if test="userId!=null and userId!=''">
                        and (doctorId_from=#{userId} or doctorId_to=#{userId}) 
            </if>
            <if test="createDate!=null and createDate!='' || createDate1!=null and createDate1!=''">
                        and CreateDate between '${createDate}' and DATE_ADD('${createDate1}',interval 1 day)
            </if>
                       order by d.createDate desc
                       limit #{start},#{rows};

                                这是我的原来的sql

可以尝试着把这个if用switch case的方式代替 ，

    private int [] getStatus(int status){
    String str = "";
  switch(status){
        case 1:
                    str = "1";
                    break;
        case 4:
                    str = "4,5,7"
                    break;
        case 5:
                    str = "5,7,8"
                    break;
   }
      // 返回status数组
 return str.split(",");
 }

xml里面直接
where status in
<foreach collection="status" item="status" open="(" separator="," close=")">
        #{status}
</foreach>

 select  *  from  tableName  where 1=1  
         <if test="status==4">
             and  Status in (4,5,7)
        </if>  

注意一下数据类型

select * from table where 1=1 and status in (4,5,7)

要看一下数据类型和排序

select * from tableName where 1=1

 <if test="status!='4'">
             and  Status=#{Status}
        </if>
         <if test="status=='4'">
             and  Status in (4,5,7)
        </if>

select d.transferID,d.PatientName,d.Mobile,d.CreateDate,d.doctorName_from,d.doctorId_from,
d.Status,
d.doctorId_to,d.doctorName_to,d.HospitalId_to,d.HospitalName_to
from his_transfertreatmentrecord d
where 1=1

and IF(#{status,jdbcType=VARCHAR}=4, Status in('4','5','7'),Status=#{status,jdbcType=VARCHAR})


and doctorName_from like CONCAT('%',#{doctorNameFrom,jdbcType=VARCHAR},'%')


and patientName like CONCAT('%',#{patientName,jdbcType=VARCHAR},'%')

        <if test="userId!=null and userId!=''">
                    and (doctorId_from=#{userId} or doctorId_to=#{userId}) 
        </if>
        <if test="createDate!=null and createDate!='' || createDate1!=null and createDate1!=''">
                    and CreateDate between '${createDate}' and DATE_ADD('${createDate1}',interval 1 day)
        </if>
                   order by d.createDate desc
                   limit #{start},#{rows};

运行一下试试看？我懂mysql但不懂java，大概摸索了下代码应该就这样子。我的逻辑是把status判断整合在一块，只要非null及空字符，即可通过sql语句进行一系列判断得出结果。

 <if test="status == '4'">
                                                or Status ='4'
                                                or Status ='5'
                                                or Status ='7'
 </if>

你原来的sql换个顺序呢，不为空且不为4，就取传入的状态值。为4就取 4、5、7的状态值

 <if test="status!=null and status!='' and status!='4'">
                        and Status = #{status,jdbcType=VARCHAR}
 </if>
 <if test="status == '4'">
                        and Status in ('4','5','7')
</if>