mysql - OR 其他条件之间的冲突

Question

我有以下查询:

    SELECT * FROM `Contacts` 
WHERE `Zona` = '1' 
AND `Responsable` = '9' 
AND `AllowComercialVisit` = 'Call_Again'
-- the problem are OR's --
OR `AllowComercialVisit` = 'Busy' 
OR `AllowComercialVisit` = 'Not_answered' 
-- the problem are OR's --
AND `DateRecall` <= '2016-06-20 13:04:52' 
AND `DateRecall` >= '2016-06-20 12:39:52' 
ORDER BY `DateRecall` ASC LIMIT 1

问题是查询应该仅显示第一个和第二个 'DateRecall' 之间的行，但返回所有带有 'Call_Again'、'Busy' 和 'Not_answered' 的行而不过滤日期。

任何解决方案将不胜感激!

Answer 1

一个简单的IN()会解决这个问题:

SELECT * FROM `Contacts` 
WHERE `Zona` = '1' 
    AND `Responsable` = '9' 
    AND `AllowComercialVisit` IN ('Call_Again','Busy','Not_answered') 
    AND `DateRecall` BETWEEN '2016-06-20 12:39:52'
                         AND '2016-06-20 13:04:52' 
ORDER BY `DateRecall` ASC
LIMIT 1

一般来说， AND 优先于 OR ，当使用 OR 尝试使用括号 ->

WHERE COND1 AND COND2 AND (COND3 OR COND4) AND COND5

这将强制优化器遵循您的优先级而不是默认优先级。