MySQL分组的OR和AND子句

Question

因此，生产者表模式如下：

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

我希望有一个查询可以做到以下内容：

选择所有生产商的 producer_id，这些生产商要么是农场、餐厅或者分销商，并且这些 producer_id 分别为 11895、11976 和 11457。

目前我有以下代码：

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子句的查询中。

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

- yretuta

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 条件时，始终使用括号！

- Bohemian

---

嗯...你有什么想法，如何让它与CI的Active Record一起工作？ - yretuta

如果您无法控制SQL语法，请尝试创建一个具有额外列的视图：create view producer_plus as select *, is_restaurant or is_farm or is_distributor as is_producer from producer。您仍然可以更新它（因为它不是连接），但当然您将无法写入计算列。 - Bohemian

这个答案差点让我走了弯路，实际上AND的优先级与其他语言中的常规相同。 - hlev

@hlev 嗯...哎呀！是的 - 我的意思就是这样。我的例子是正确的。我已经修复了错误。谢谢你让我知道。 - Bohemian

Answer 2

这应该能解决问题：

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 `pd`.`producer_id` IN ('181176', '181180', '176080', '5')
AND (`is_distributor` = 1 OR `is_restaurant` = 1 OR `is_farm` = 1)
GROUP BY `ad`.`address_id`
LIMIT 10

- riku