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

Question

我的表中有以下名为 cat_product 的数据

cat_id product_id
1          2
1          3
2          2
2          4

如果给定 product_id (2,3) 的一组值，我想知道唯一的 cat_id。在这种情况下，这将是 cat_id 1。

我应该如何构造mysql查询？

我试过

select distinct cat_id from cat_product where product_id IN (2,3) 

但它同时返回 1 和 2。

如果我用

select distinct cat_id from cat_product where product_id NOT IN (2,3) 

我得到 2。

有没有比

更好的方法

   select distinct cat_id from cat_product where product_id IN (2,3) 
and cat_id NOT IN 
(select distinct cat_id from cat_product where product_id NOT IN (2,3) )

我需要返回具有我正在寻找的确切产品 ID 集的 category_id。

基本上我有大约 10 个产品 ID 作为输入。

Answer 1

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
        )

您需要在 (cat_id, product_id) 上有一个 UNIQUE 索引(按此顺序)才能快速运行。

此解决方案将使用 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)
        )