如何从 x 等于多个值的位置进行选择？

我正在调试一些代码，遇到了以下 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类型的广告。

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

如果您能提供正确的帮助，我将不胜感激。

254627 次浏览
小开
最佳答案

在“ 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)
小开

您可以尝试在 OR 表达式周围使用括号，以确保正确解释查询，或者更简洁地说，使用 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)
小开

使用 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)

提交答案