如何从x等于多个值的地方进行选择?

I am debugging some code and have encountered the following SQL query (simplified version):

我正在调试一些代码，遇到了以下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

I'm getting very strange results from the query and I think its because the first OR is negating the AND operators that are found before it.

我从查询中得到了非常奇怪的结果，我认为这是因为第一个或者是否定前面的和运算符。

This results in getting results back for ads of all types and not just for the type 13.

这导致了所有类型的广告的结果，而不仅仅是13型的。

Each time the query is called there may be a differnt amount of county entities that need to be looked up.

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

Any help on the correct way to go about this would be appreciated.

对于正确的解决方法的任何帮助都是值得赞赏的。

3 个解决方案

#1

105

Put parentheses around the "OR"s:

在"或"s:

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 even better, use 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)

#2

You can try using parentheses around the OR expressions to make sure your query is interpreted correctly, or more concisely, use IN:

您可以尝试在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 IN (2,5,7,9)

#3

And even simpler using 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)

#1

105