喜欢查询不适用于 WHERE 和多个 OR WHERE

Question

我有带有WHERE的连接语句和带有LIKE的多个OR WHERE，以下是我尝试过的，不幸的是，即使使用无效的LIKE值，它也无法正常工作，例如LIKE %0000%，我确定给定的 like 查询中没有这样的相关数据，但下面的 sql 仍然返回行。任何帮助，想法将不胜感激。

SELECT `a`.`id`, `a`.`subcategory_id`, `a`.`ps_id`, `b`.`id` AS `b_id`, `b`.`ps_keyword`, `b`.`created_at`
FROM `buss_subcategory_products_services` `a`
LEFT JOIN `buss_products_services` `b` ON `b`.`id` = `a`.`ps_id`
WHERE `b`.`is_deleted` =0
AND `a`.`subcategory_id` = '11'
OR   (
`a`.`subcategory_id` = '1'
OR `a`.`subcategory_id` = '8'
OR `a`.`subcategory_id` = '5'
OR `a`.`subcategory_id` = '2'
OR `a`.`subcategory_id` = '3'
OR `a`.`subcategory_id` = '9'
OR `a`.`subcategory_id` = '4'
OR `a`.`subcategory_id` = '10'
OR `a`.`subcategory_id` = '6'
OR `a`.`subcategory_id` = '7'
)
AND  `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'
ORDER BY `b`.`ps_keyword` ASC

Answer 1

这看起来像是一个逻辑先见之明问题。

这：

WHERE 
`b`.`is_deleted` =0
AND `a`.`subcategory_id` = '11'
OR  (
`a`.`subcategory_id` = '1'
OR `a`.`subcategory_id` = '8'
OR `a`.`subcategory_id` = '5'
OR `a`.`subcategory_id` = '2'
OR `a`.`subcategory_id` = '3'
OR `a`.`subcategory_id` = '9'
OR `a`.`subcategory_id` = '4'
OR `a`.`subcategory_id` = '10'
OR `a`.`subcategory_id` = '6'
OR `a`.`subcategory_id` = '7'
)
AND `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'

可能应该写：

WHERE 
`b`.`is_deleted` =0
AND (
`a`.`subcategory_id` = '11'
OR `a`.`subcategory_id` = '1'
OR `a`.`subcategory_id` = '8'
OR `a`.`subcategory_id` = '5'
OR `a`.`subcategory_id` = '2'
OR `a`.`subcategory_id` = '3'
OR `a`.`subcategory_id` = '9'
OR `a`.`subcategory_id` = '4'
OR `a`.`subcategory_id` = '10'
OR `a`.`subcategory_id` = '6'
OR `a`.`subcategory_id` = '7'
)
AND `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'

我建议将同一列上的这些多个OR条件替换为IN：

WHERE
`b`.`is_deleted` = 0
AND `a`.`subcategory_id` IN (11, 1, 8, 5, ..., 7)
AND `b`.`ps_keyword` LIKE '%xxxxxxxxxxxxxxxxxxxxxxxxxxxx%' ESCAPE '!'

请注意，我将值列表更改为数字而不是字符串 - 这些值看起来像数字，因此应按数字存储和处理它们。