我在调试一些代码，遇到如下SQL查询(简化版):

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id = 2
OR ads.county_id = 5
OR ads.county_id = 7
OR ads.county_id = 9

我从查询中得到了非常奇怪的结果，我认为这是因为第一个 OR 否定了在它之前找到的 AND 运算符。

这会返回所有类型的广告的结果，而不仅仅是类型 13。

每次调用查询时，可能需要查找不同数量的县实体。

任何有关正确方法的帮助将不胜感激。

最佳答案

在“OR”周围加上括号:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND
(
    ads.county_id = 2
    OR ads.county_id = 5
    OR ads.county_id = 7
    OR ads.county_id = 9
)

或者更好的是，使用 IN:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id IN (2, 5, 7, 9)

https://stackoverflow.com/questions/261783/