mysql:选择查询同一列的多个条件不工作

ars1skjm  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(362)

我想做搜索与过滤系统,如亚马逊,易趣或souq。此表包含所有产品的所有值和键“go\u products\u cats\u options”和 pco_option_id in(41,31,32,30)是过滤器的键,这个数字 pco_answer_id in('312','250','253','240')是这个键的值
我想搜索这个键和值,不是用或,而是用我的语句,所以我能做什么

SELECT
        `go_products`.*,
        `go_users`.`u_username`,
        `go_users`.`u_id`
    FROM
        `go_products`
    INNER JOIN `go_users` ON `go_users`.`u_id` = `go_products`.`product_store_id`
    INNER JOIN `go_products_cats_options` ON `go_products_cats_options`.`pco_product_id` = `go_products`.`product_id`
    WHERE
        `go_users`.`u_is_active` = 1 AND `go_users`.`u_store_is_expire` = 0 AND `go_products`.`product_cat_id` IN('39') AND `product_active` = 1 AND(
            `pco_option_id` IN(41, 31, 32, 30) AND `pco_answer_id` IN('312', '250', '253', '240')
        )
    HAVING
        COUNT(*) > 4
    ORDER BY
        `product_id` ASC
    LIMIT 40

当我过去工作不成功的时候

lf5gs5x2

lf5gs5x21#

没有分组依据就不能准备语句。在具有关键字之前,必须使用group by子句。您可以使用:

SELECT 

    `go_products`.*,
    `go_users`.`u_username`,
    `go_users`.`u_id`
FROM
    `go_products`
INNER JOIN `go_users` ON `go_users`.`u_id` = `go_products`.`product_store_id`
INNER JOIN `go_products_cats_options` ON `go_products_cats_options`.`pco_product_id` = `go_products`.`product_id`
WHERE
    `go_users`.`u_is_active` = 1 AND `go_users`.`u_store_is_expire` = 0 AND `go_products`.`product_cat_id` IN('39') AND `product_active` = 1 AND(
        `pco_option_id` IN(41, 31, 32, 30) AND `pco_answer_id` IN('312', '250', '253', '240')
    )
GROUP BY u_username
HAVING
    COUNT(*) > 4
ORDER BY
    `product_id` ASC
LIMIT 40

相关问题