带有if语句的MySQL存储过程

Question

我在 MySQL 中有以下存储过程

SELECT *
    FROM rewards
        LEFT JOIN tasks
        ON tasks.id = rewards.task_id
        AND rewards.received_at = received_Date
    WHERE tasks.kid_id = kid_Id
    ORDER BY tasks.id ASC;

存储过程有 2 个 (IN) 输入，kid_Id(整数)和 received_Date(日期)，并且工作正常。

问题:我想要的是，如果 received_Date 是 NULL 那么我想查看所有日期，这可能吗？

换句话说:

AND rewards.received_at = received_Date 只有在我通过 received_Date 时才有效，否则返回所有日期。

Answer 1

试试这个。如果received_Date 为null

，则在 where 子句 中使用 OR 运算符获取所有行

SELECT *
    FROM rewards
        LEFT JOIN tasks
        ON tasks.id = rewards.task_id
                WHERE tasks.kid_id = kid_Id 
                AND (rewards.received_at = received_Date or received_Date = 0) 
    ORDER BY tasks.id ASC;