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

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

数据库表中，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>

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>


SELECT * FROM order_dept WHERE order_status != 3
UNION
SELECT * FROM order_dept WHERE order_status = 3 AND updateTime < now

SELECT * FROM order_dept where order_status= 3 and updateTime < NOW();

SELECT * FROM order_dept where order_status= 3 and DATEDIFF(updateTime ,sysdate())<1
小于一天

用如下代码替换掉试试，即在最后一行代码添加了一条判断

<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>
            <if test="orderStatus != null  and orderStatus == 3"> and order_status = #{orderStatus} and dateDiff(updateTime ,NOW()) <= 1</if>
        </where>
    </select>

替换这一行试试


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

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

            <if test="orderStatus == 3 ">
                <![CDATA[
                and update_time < date_add(now(),INTERVAL -1 DAY)
            ]]>
            </if>

img