MySQL 8：如何在关联表中查找Bag的超集（含全部子项）

问题核心 #

要找出作为指定base bag超集的bag，必须满足目标bag包含base bag的所有item（可额外包含其他item），而非仅匹配至少一个item。以下是针对不同场景的解决方案：

先明确表结构示例 #

假设表数据如下（方便验证查询效果）：

• bag表：

  id	name
  1	Bag1
  2	Bag2
  3	Bag3
  4	Bag4

• item表：

  id	name
  1	Item1
  2	Item2
  3	Item3

• bag_item关联表：

  bag_id	item_id
  1	1
  1	2
  2	1
  2	2
  2	3
  3	1
  4	3

1. 单个Base Bag的超集查询 #

以base_bag_id=1为例，核心逻辑是验证目标bag与base bag的共同item数，等于base bag的总item数，确保所有item都被覆盖：

SELECT 
  b.id AS superset_bag_id,
  b.name AS superset_bag_name
FROM bag b
JOIN bag_item bi ON b.id = bi.bag_id
-- 关联base bag的所有item
JOIN (SELECT item_id FROM bag_item WHERE bag_id = 1) base_items 
  ON bi.item_id = base_items.item_id
GROUP BY b.id, b.name
-- 关键条件：匹配到的base item数量 = base bag的总item数
HAVING COUNT(DISTINCT bi.item_id) = (
  SELECT COUNT(DISTINCT item_id) FROM bag_item WHERE bag_id = 1
)
-- 可选：排除base bag自身（若不需要将自身视为超集）
-- AND b.id != 1;

执行结果会返回Bag1、Bag2，Bag3因只包含Item1会被过滤，解决了之前的错误。

2. 批量Base Bag的超集查询 #

支持同时查询多个base bag（如base_bag_id=1,4），返回每个base bag对应的所有超集：

SELECT 
  base_bag.id AS base_bag_id,
  base_bag.name AS base_bag_name,
  superset_bag.id AS superset_bag_id,
  superset_bag.name AS superset_bag_name
FROM bag base_bag
-- 预计算每个base bag的总item数
JOIN (
  SELECT bag_id, COUNT(DISTINCT item_id) AS total_items
  FROM bag_item
  WHERE bag_id IN (1,4)
  GROUP BY bag_id
) base_item_count ON base_bag.id = base_item_count.bag_id
-- 关联所有可能的superset bag
JOIN bag superset_bag
-- 匹配superset bag与base bag的共同item
JOIN bag_item superset_bi ON superset_bag.id = superset_bi.bag_id
JOIN bag_item base_bi ON base_bag.id = base_bi.bag_id 
  AND superset_bi.item_id = base_bi.item_id
GROUP BY 
  base_bag.id, base_bag.name,
  superset_bag.id, superset_bag.name
-- 验证共同item数等于base bag的总item数
HAVING COUNT(DISTINCT superset_bi.item_id) = base_item_count.total_items
-- 可选：排除base bag自身
-- AND superset_bag.id != base_bag.id;

执行结果：

• base bag 1 → superset bag 1、2
• base bag 4 → superset bag 2、4

3. 全量Bag超集关系查询 #

生成所有bag之间的超集关系表，包含空bag的特殊处理（空bag的超集是所有bag）：

SELECT 
  b1.id AS base_bag_id,
  b1.name AS base_bag_name,
  b2.id AS superset_bag_id,
  b2.name AS superset_bag_name
FROM bag b1
-- 预计算每个bag的总item数
JOIN (
  SELECT bag_id, COUNT(DISTINCT item_id) AS total_items
  FROM bag_item
  GROUP BY bag_id
) b1_item_count ON b1.id = b1_item_count.bag_id
JOIN bag b2
-- 关联item匹配关系
LEFT JOIN bag_item bi1 ON b1.id = bi1.bag_id
LEFT JOIN bag_item bi2 ON b2.id = bi2.bag_id AND bi1.item_id = bi2.item_id
GROUP BY b1.id, b1.name, b2.id, b2.name
HAVING 
  -- 空bag的特殊处理：所有bag都是其超集
  (b1_item_count.total_items = 0)
  OR
  -- 非空bag：共同item数等于base bag的总item数
  (COUNT(DISTINCT bi2.item_id) = b1_item_count.total_items)
-- 可选：排除自身
-- AND b1.id != b2.id;

错误原因说明 #

之前的查询仅通过简单关联判断存在匹配item，没有验证所有base bag的item都被包含。通过统计匹配item的数量与base bag总item数相等，才能确保目标bag是真正的超集。

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