如何在MySQL中查询同时匹配多个category_id的记录？

当需要找出同时拥有多个分类ID（比如1和3）的条目时，IN查询只能匹配任一分类，以下是几种可行的解决办法：

方法1：分组统计匹配数量 #

这是最常用的方案，通过分组后统计匹配的分类数量，筛选出数量等于目标分类总数的条目。假设关联表为item_category，主表为items，关联字段是item_id：

-- 先筛选出符合条件的item_id
SELECT ic.item_id
FROM item_category ic
WHERE ic.category_id IN (1, 3)
GROUP BY ic.item_id
-- 目标分类有2个，所以数量要等于2
HAVING COUNT(DISTINCT ic.category_id) = 2;

如果需要获取主表的完整数据，再关联主表即可：

SELECT i.*
FROM items i
JOIN (
    SELECT ic.item_id
    FROM item_category ic
    WHERE ic.category_id IN (1, 3)
    GROUP BY ic.item_id
    HAVING COUNT(DISTINCT ic.category_id) = 2
) filtered_items ON i.id = filtered_items.item_id;

注：用COUNT(DISTINCT)是为了避免同一条目重复关联同一分类的情况，若数据不会重复，也可以直接用COUNT(*)。

方法2：自连接查询 #

通过多次自连接关联表，每次匹配一个分类ID，确保同一条目同时满足所有分类条件：

SELECT DISTINCT i.*
FROM items i
JOIN item_category ic1 ON i.id = ic1.item_id AND ic1.category_id = 1
JOIN item_category ic2 ON i.id = ic2.item_id AND ic2.category_id = 3;

这种方式适合分类数量较少的场景，分类过多时自连接次数会增加，效率可能受影响。

方法3：EXISTS子查询 #

对每个目标分类分别做存在性判断，确保条目同时关联所有目标分类：

SELECT i.*
FROM items i
WHERE EXISTS (
    SELECT 1 FROM item_category ic 
    WHERE ic.item_id = i.id AND ic.category_id = 1
)
AND EXISTS (
    SELECT 1 FROM item_category ic 
    WHERE ic.item_id = i.id AND ic.category_id = 3
);

如果item_category表有(item_id, category_id)的联合索引，这种方法的查询效率会很高，逻辑也直观易懂。

内容的提问来源于stack exchange，提问作者user16462399