我有一个存储过程来检查在我的数据库中预订的假期,它不应该考虑状态为“已拒绝”或“已取消”的假期,但我的数据库中只有一个状态为拒绝的记录但是这个select语句返回1?

        SELECT COUNT(*) JobRoleID
        FROM        Employees
        RIGHT JOIN  Holidays
        ON          Employees.ID = Holidays.EmployeeID
        WHERE       Holidays.Status <> 'Declined' AND Holidays.Status <> 'Cancelled'
        AND        (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/30')
        OR         (Holidays.Startdate >= '2014/04/28' AND Holidays.Enddate <= '2014/04/30')
        OR         (Holidays.Startdate <= '2014/04/30' AND Holidays.Enddate >= '2014/04/30')
        OR         (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/28')
        OR         (Holidays.StartDate = '2014/04/28' AND Holidays.EndDate = '2014/04/30')

最佳答案

这里需要考虑运算符的优先级。

Not -->  AND --> OR

Not 优先于 AND ,AND 优先于 OR。对于这些嵌套的 AND 和 OR,最好将它们放在括号中。
WHERE       Holidays.[Status] <> 'Declined' AND Holidays.[Status] <> 'Cancelled'
AND
    (       (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/30')
        OR  (Holidays.Startdate >= '2014/04/28' AND Holidays.Enddate <= '2014/04/30')
        OR  (Holidays.Startdate <= '2014/04/30' AND Holidays.Enddate >= '2014/04/30')
        OR  (Holidays.Startdate <= '2014/04/28' AND Holidays.Enddate >= '2014/04/28')
        OR  (Holidays.StartDate = '2014/04/28' AND Holidays.EndDate = '2014/04/30')
    )

还要避免使用 Sql server 关键字作为列名,如果在 sql 中使用它们时确实有一些使用方括号 [] 将它们括起来。

关于mysql - 存储过程返回错误值?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/22839415/

10-12 12:43
查看更多