如何编写SQL查询筛选同时属于多个分类的内容项？

现有两张表：items（内容项表）和item_categories（内容项与分类的关联表），需要编写SQL查询同时属于指定所有分类（而非属于任意分类）的内容项。

已尝试的方法及问题 #

• 使用IN子句关联查询：返回的是属于任意指定分类的内容项，不符合需求
• 用AND同时匹配多个category_id：单条关联记录的category_id无法同时等于多个值，结果始终为空
• 多IN子查询嵌套：能实现需求，但写法冗余繁琐

表结构 #

-- 内容项表
CREATE TABLE `items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  [...]
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 内容项-分类关联表
CREATE TABLE `item_categories` (
  `item_id` bigint(20) DEFAULT NULL,
  `category_id` bigint(20) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

尝试过的SQL示例 #

• 筛选关联表中属于指定分类的记录：

SELECT `item_categories`.* FROM `item_categories`
  WHERE `item_categories`.`category_id` IN (11811, 11911)

• 关联items表得到属于任意分类的内容项：

SELECT DISTINCT `items`.* FROM `items`
  INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
  WHERE `item_categories`.`category_id` IN (11811, 11911)

• 使用AND的错误尝试：

SELECT DISTINCT `items`.* FROM `items`
  INNER JOIN `item_categories` ON `item_categories`.`item_id` = `items`.`id`
  WHERE `item_categories`.`category_id` = 11811
    AND `item_categories`.`category_id` = 11911

• 可实现需求但繁琐的写法：

SELECT DISTINCT `items`.* FROM `items`
  WHERE ID IN (
    SELECT `item_categories`.item_id FROM `item_categories`
      WHERE `item_categories`.`category_id` = 11811
  )
  AND ID IN (
    SELECT `item_categories`.item_id FROM `item_categories`
      WHERE `item_categories`.`category_id` = 11911
  )

简洁实现方案 #

方案1：GROUP BY + HAVING（推荐）

思路：先筛选出属于目标分类的关联记录，按item_id分组后，筛选分组内的记录数等于指定分类的数量——这就保证该item_id同时匹配了所有指定分类，最后关联items表获取完整内容项信息。

SELECT i.*
FROM items i
JOIN item_categories ic ON i.id = ic.item_id
WHERE ic.category_id IN (11811, 11911) -- 指定需要匹配的分类ID集合
GROUP BY i.id
HAVING COUNT(DISTINCT ic.category_id) = 2; -- 这里的数字要和指定的分类数量一致

注：如果item_categories表中不存在同一个item_id对应同一个category_id的重复记录，可以去掉DISTINCT，直接用COUNT(ic.category_id)。

方案2：自连接

思路：每增加一个需要匹配的分类，就做一次自连接，确保item_id在每个分类的关联记录中都存在。

SELECT DISTINCT i.*
FROM items i
JOIN item_categories ic1 ON i.id = ic1.item_id AND ic1.category_id = 11811
JOIN item_categories ic2 ON i.id = ic2.item_id AND ic2.category_id = 11911;

注：如果需要匹配更多分类，继续添加对应的JOIN item_categories icN ON ...即可。

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