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

Question

            上面这张图如果前台给我传一个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

Answer 1

可以尝试着把这个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>