MySQL 分组 OR 和 AND 子句

Question

所以生产者表模式是这样的:

producer_id = int PK
producer_name = varchar
is_restaurant = boolean
is_farm = boolean
is_distributor = boolean

我想要一个执行此操作的查询:

select all producers whose producer_id are in (11895, 11976, 11457) which is either a farm, a restaurant or a distributor

我有这个:

SELECT `pd`.`producer_id` AS producer_id, `pd`.`producer` AS producer, `pd`.`is_restaurant`, `pd`.`is_restaurant_chain`, `pd`.`is_farm`, `pd`.`is_farmers_market`, `pd`.`is_distributor`, `pd`.`is_manufacture`, `ad`.`address_id`, `ad`.`address`, `ad`.`state_id`, `ad`.`city`, `ad`.`city_id`, `ad`.`country_id`, `ad`.`zipcode`, `ad`.`latitude`, `ad`.`longitude`, `cu`.`custom_url` AS custom_url
FROM (`producer` AS pd)
JOIN `address` AS ad ON `pd`.`producer_id`=`ad`.`producer_id`
LEFT JOIN `custom_url` AS cu ON `cu`.`producer_id`=`pd`.`producer_id`
WHERE `is_farm` = 1
OR `is_distributor` = 1
OR `is_restaurant` = 1
AND `pd`.`producer_id` IN ('181176', '181180', '176080', '5') 
GROUP BY `ad`.`address_id`
LIMIT 10

但查询只对 is_farm、is_distributor 和 is_restaurant 应用 WHERE 条件，不对 producer_id 应用 AND 子句。

我想知道我是否可以使用子查询来执行“餐厅是分销商还是农场”并将其插入带有 AND 子句的查询中。

我该怎么做才能解决这个问题？

Answer 1

你的问题是在 SQL 中，AND 优先于 OR，所以你只需要括号:

SELECT `pd`.`producer_id` AS producer_id, `pd`.`producer` AS producer, `pd`.`is_restaurant`, `pd`.`is_restaurant_chain`, `pd`.`is_farm`, `pd`.`is_farmers_market`, `pd`.`is_distributor`, `pd`.`is_manufacture`, `ad`.`address_id`, `ad`.`address`, `ad`.`state_id`, `ad`.`city`, `ad`.`city_id`, `ad`.`country_id`, `ad`.`zipcode`, `ad`.`latitude`, `ad`.`longitude`, `cu`.`custom_url` AS custom_url
FROM (`producer` AS pd)
JOIN `address` AS ad ON `pd`.`producer_id`=`ad`.`producer_id`
LEFT JOIN `custom_url` AS cu ON `cu`.`producer_id`=`pd`.`producer_id`
WHERE (`is_farm` = 1 -- NOTE: Opening bracket to bracket up your OR terms together
OR `is_distributor` = 1
OR `is_restaurant` = 1) -- NOTE: Closing bracket
AND `pd`.`producer_id` IN ('181176', '181180', '176080', '5') 
GROUP BY `ad`.`address_id`
LIMIT 10

推论:

在 SQL 中，A OR B OR C AND D 被解析为 (A OR (B OR (C AND D)))。应用括号将更正此常见错误，即 (A OR B OR C) AND D。

记住:混合使用 AND 和 OR 术语时，请始终使用方括号!