关于#mysql#的问题：数据库表中，order_status（工作状态）字段，还有一个字段是updateTime（更新时间）字段

Question

问题遇到的现象和发生背景

数据库表中，order_status（工作状态）字段，有4种状态（0,1,2,3），还有一个字段是updateTime（更新时间）字段

我想要达到的结果

查询全部状态，遇到order_status= 3 时，判断updateTime是否比现在小于一天，如果是则保留数据，否则不显示。

在不修改下面的代码添加：

<sql id="selectDeptVo">
        select order_id, order_uid, order_salename,order_customer, order_pic, order_dept, order_deadline,start_time,end_time, order_status, order_describe, update_time, create_time from order_dept
    </sql>

    <select id="selectDeptList" parameterType="Dept" resultMap="DeptResult">
        <include refid="selectDeptVo"/>
        <where>  
            <if test="orderUid != null "> and order_uid = #{orderUid}</if>
            <if test="orderSalename != null  and orderSalename != ''"> and order_salename like concat('%', #{orderSalename}, '%')</if>
            <if test="orderCustomer != null  and orderCustomer != ''"> and order_customer like concat('%', #{orderCustomer}, '%')</if>
            <if test="orderPic != null  and orderPic != ''"> and order_pic = #{orderPic}</if>
            <if test="orderDept != null  and orderDept != ''"> and order_dept = #{orderDept}</if>
            <if test="orderDeadline != null "> and order_deadline = #{orderDeadline}</if>
            <if test="startTime != null "> and start_time = #{startTime}</if>
            <if test="endTime != null "> and end_time = #{endTime}</if>
            <if test="orderStatus != null  and orderStatus != ''"> and order_status = #{orderStatus}</if>
            <if test="orderDescribe != null  and orderDescribe != ''"> and order_describe = #{orderDescribe}</if>
        </where>
    </select>

Answer 1

SELECT * FROM order_dept WHERE order_status != 3 or (order_status = 3 and DATEDIFF(updateTime,NOW())=-1)
直接用SQL语句就查出来了，干嘛还要判断？

    <if test="orderStatus != null  and orderStatus == 3"> and order_status = #{orderStatus} and dateDiff(updateTime ,NOW()) <= 1</if>

换成

<if test="orderStatus != null  "> and order_status = #{orderStatus} and （order_status != 3 or (order_status = 3 and dateDiff(updateTime ,NOW()) <= 1)</if>