我应该使用什么mysql查询来选择符合我所有条件的类别?

SELECT  cat_id
FROM    (
        SELECT  DISTINCT cat_id
        FROM    cat_product
        ) cpo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    cat_product cpi
        WHERE   cpi.cat_id = cpo.cat_id
                AND product_id IN (2, 3)
        LIMIT 1, 1
        )

你需要有一个 UNIQUE 索引 (cat_id, product_id) (按此顺序)使其快速工作。

此解决方案将使用 INDEX FOR GROUP BY 获取不同类别的列表,以及 EXISTS 谓词将比 COUNT(*) (因为聚合需要一些开销)。

如果要搜索两个以上的产品,请将第一个参数调整为 LIMIT 因此。

应该是 LIMIT n - 1, 1 ,其中 n 是中的项目数 IN 名单。

更新:

若要返回包含列表中所有产品的类别,而不返回其他类别,请使用以下命令:

SELECT  cat_id
FROM    (
        SELECT  DISTINCT cat_id
        FROM    cat_product
        ) cpo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    cat_product cpi
        WHERE   cpi.cat_id = cpo.cat_id
                AND product_id IN (2, 3)
        LIMIT 1, 1
        )
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    cat_product cpi
        WHERE   cpi.cat_id = cpo.cat_id
                AND product_id NOT IN (2, 3)
        )

SELECT * FROM
  (SELECT cat_id FROM cat_product WHERE product_id=2) a
INNER JOIN
  (SELECT cat_id FROM cat_product WHERE product_id=3) b
ON a.cat_id = b.cat_id;

你自己的解决办法行不通(至少如果我正确理解这个问题的话)。它将返回包含 一个或多个 所列产品和 不 其他产品。尝试将以下行添加到表中,并查看是否获得预期结果:

insert into cat_product (cat_id, product_id) values (1,5)

如果你真的需要找到 全部的 在列出的产品中(无论类别中可能有哪些其他产品),请尝试以下查询:

select cat_id
from cat_product
where product_id in (2,3)
group by cat_id
having count(*) = 2

号码 2 查询的最后一行对应于要搜索的产品集的大小。如果使用参数化的api执行查询,请确保创建绑定到 productsArray.length 或类似的。

找到包含所有给定产品和其他内容的猫ID。

让我们创建一些测试数据…

create table cat_product (
    cat_id int not null,
    product_id int not null,
    primary key (cat_id, product_id));

delete from cat_product;
insert into cat_product
values
    (1, 2),
    (1, 3),
    (2, 2),
    (2, 4),
    (3, 1),
    (3, 2),
    (3, 3),
    (3, 4),
    (3, 5),
    (4, 1),
    (4, 2),
    (4, 3),
    (4, 4),
    (4, 6),
    (5, 1),
    (5, 2),
    (5, 3),
    (5, 4),
    (5, 5),
    (5, 6);

插入您的产品ID列表 cp.product_id IN (1, 2, 3, 4, 5)
和 在查询的最后一行插入产品ID的编号 cats.match_count = 5 。

select
    cats.cat_id
from
    /* Count how many products for each cat match the list of products */
    (select
        cp.cat_id,
        count(*) as match_count
    from
        cat_product cp
    where
        cp.product_id IN (1, 2, 3, 4, 5)
    group by
        cp.cat_id) as cats,
    /* Count the number of products in each cat */
    (select cat_id, count(*) as cat_count
    from cat_product
    group by cat_id) as cat_count
where
    cats.cat_id = cat_count.cat_id
    /* We matched all the products in the cat */
    and cats.match_count = cat_count.cat_count
    /* We matched all the products we wanted.  Without this clause 
       the query would also match cats that only contain products in 
       our list (but not all of them) */
    and cats.match_count = 5;

Relational Division 是你要找的