具有多个AND OR WHERE条件的复杂MySQL查询
具有多个AND OR WHERE条件的复杂MySQL查询
提问于 2013-01-13 11:03:58
我有一张桌子，可以帮你挑选商品。
我希望有一个查询，其中我返回了每个记录，其中：
category_id = 'M10' and band_id = '010'
OR
category_id = 'M10' and band_id = '020'
AND
category_id = 'M50' and band_id = '010'
OR
category_id = 'M50' and band_id = '020'我的问题是：
SELECT
product.entry_id AS entry_id,
helpme.item_id AS product_id
FROM inbound_help_me_choose helpme
LEFT JOIN exp_channel_titles product ON (product.product_id = helpme.item_id)
WHERE
helpme.submarket_id = 'NZ'
AND
(
(helpme.category_id = 'M10' AND helpme.band_id = '010')
OR
(helpme.category_id = 'M10' AND helpme.band_id = '020')
)在第一个类别上工作得很好，但只要我在条件失败的地方添加新的：
SELECT
product.entry_id AS entry_id,
helpme.item_id AS product_id
FROM inbound_help_me_choose helpme
LEFT JOIN exp_channel_titles product ON (product.product_id = helpme.item_id)
WHERE
helpme.submarket_id = 'NZ'
AND
(
(helpme.category_id = 'M10' AND helpme.band_id = '010')
OR
(helpme.category_id = 'M10' AND helpme.band_id = '020')
)
AND
(
(helpme.category_id = 'M50' AND helpme.band_id = '010')
OR
(helpme.category_id = 'M50' AND helpme.band_id = '020')
OR
(helpme.category_id = 'M50' AND helpme.band_id = '030')
)这对我来说似乎还可以，所以我完全不知道为什么它不能工作。
回答 4
Stack Overflow用户
回答已采纳
发布于 2013-01-13 11:07:42
我觉得你需要这个
SELECT
product.entry_id AS entry_id,
helpme.item_id AS product_id
FROM inbound_help_me_choose helpme
LEFT JOIN exp_channel_titles product ON (product.product_id = helpme.item_id)
WHERE
helpme.submarket_id = 'NZ'
AND
(
(helpme.category_id = 'M10' AND helpme.band_id IN ('010','020')) OR
(helpme.category_id = 'M50' AND helpme.band_id IN ('010','020','030'))
)Stack Overflow用户
发布于 2013-01-13 11:12:05
稍微更改一下您需要的内容：
category_id = 'M10' and band_id = '010' OR category_id = 'M10' and band_id = '020'
AND
category_id = 'M50' and band_id = '010' OR category_id = 'M50' and band_id = '020'与以下内容相同：
category_id = 'M10' and (band_id = '010' OR band_id = '020')
AND
category_id = 'M50' and (band_id = '010' OR band_id = '020')与以下内容相同：
category_id = 'M10' and category_id = 'M50'
AND
(band_id = '010' OR band_id = '020') and (band_id = '010' OR band_id = '020')category_id不能同时为M10和M50，因此您不会得到任何结果...
Stack Overflow用户
发布于 2013-01-13 11:18:41
您在问题中显示的第二个示例查询的where子句中的条件，在其where子句中遇到问题的那个，永远不会计算为真...
第一个示例中的第一个where子句要求category_id等于M10。
然后，在第二个示例中，where子句获得另一组添加了'and‘的语句。问题是这个where子句只需要category_id为M50的记录
不管逻辑上的任何其他问题，我可以相当肯定地说，您不会找到同时具有M10和M50的category_id的记录。
另一个用户@rs。发布了一个查询，其中修改了where子句，该查询满足开头或您的问题中指定的条件逻辑规则。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14300269
复制相关文章
相似问题
腾讯云开发者
