类似mysql的查询不能处理where和multiple或where

lndjwyie 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(246)

关闭。这个问题需要细节或清晰。它目前不接受答案。
**想改进这个问题吗？**通过编辑这个帖子来添加细节并澄清问题。

10个月前关门了。
改进这个问题
我有个join语句 WHERE 多个 OR WHERE 与 LIKE ，下面是我尝试过的，不幸的是，即使是无效的，它也不能正常工作 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

sql mysql Join where-clause select

来源：https://stackoverflow.com/questions/62407382/like-query-not-working-with-where-and-multiple-or-where

1条答案

按热度按时间

qnzebej01#

这看起来像是一个合乎逻辑的预测问题。
这是：

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 '!'

请注意，我将值列表改为数字，而不是字符串-这些值看起来像数字，因此它们应该被存储并作为数字对待。

赞(0）回复(0）举报 2021-07-26

我来回答

类似mysql的查询不能处理where和multiple或where

1条答案

相关问题

热门标签

最新问答