具有多个 OR 条件和单个 AND 条件的 MySQL 查询

Question

我有一个 MySQQL 数据库表，我在其中根据一些用户输入使用 PHP 动态构建 SQL 语句。

SQL 最终看起来像这样......

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
AND (
`backing_cut` =0
)

基本上 DB 列 item_status 可以是它们中的任何一个，列 backing_cut 可以是 0 或 1。

使用上面的 SQL，它返回所有与 item_status 值匹配的记录，而不管 backing_cut 值如何。

它应该允许我根据 backing_cut 的值为 0 或 1 从结果中过滤掉记录。

我做错了什么？

除了上面的SQL我也试过....

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
AND `backing_cut` =0

和

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
(
    AND `backing_cut` =0
)

Answer 1

使用括号

SELECT * 
FROM  `nam_order_items` 
WHERE (item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar')
AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)

你也可以使用数组中的值作为

SELECT * 
FROM  `nam_order_items` 
WHERE `item_status` IN  ('Glass Bending - Andy','Glass Bending - Bren','Glass Bending - Gary','Glass Bending - James','Glass Bending - Oscar')
AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)

然后您可以使用 php 构建数组并简单地执行以下操作:

$options = array('Glass Bending - Andy','Glass Bending - Bren','Glass Bending - Gary','Glass Bending - James','Glass Bending - Oscar');

$sql = "SELECT * FROM `nam_order_items` WHERE `item_status` IN ({implode(',', $options}) AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)";

编辑以添加支持剪切 = 1 或 0 的选项