如何在多个Where子句之间进行MySQL

我想显示价格在10k到30k之间的商品,其中category_id = 1或category_id = 2。

这是我的查询

SELECT * FROM products
WHERE price BETWEEN 10000 AND 30000
AND
category_id = '2' OR category_id = '3'
ORDER BY name ASC, price ASC

但我得到这些类别中的所有产品,而没有得到价格在10k到30k之间的产品

评论
择一人深爱
择一人深爱

用适当的括号加强逻辑

SELECT * FROM products
WHERE price BETWEEN 10000 AND 30000
AND
(category_id = '2' OR category_id = '3')
ORDER BY name ASC, price ASC
点赞
评论
lenim
lenim

您还需要考虑此操作的顺序,您想要此

WHERE (price BETWEEN 10000 AND 30000)
AND
(category_id = '2' OR category_id = '3')
点赞
评论
aeius
aeius

You OR predicate needs to be in between parenthesis:

SELECT * FROM products
WHERE price BETWEEN 10000 AND 30000
AND (category_id = '1' OR category_id = '2')
ORDER BY name ASC, price ASC

Alternatively, you can use IN:

SELECT * FROM products
WHERE price BETWEEN 10000 AND 30000
AND category_id IN ('1', '2')
ORDER BY name ASC, price ASC
点赞
评论
kautem
kautem

To understand why this is happening, you need to know about operator precedence. You might remember from school (I barely do) that multiplication and division are evaluated before addition - same concept here.

最好的做法(对于代码可读性和使您的意图都清晰明了)是使用括号强制按所需顺序对运算符进行评估。

点赞
评论