MySQL多对多关系下精确匹配指定ID集合的关联实体查询方法

要实现「菜谱关联食材集合和给定列表完全相等」的查询，核心要抓两个判定条件，缺一不可：

• 菜谱关联的所有食材，没有任何一个超出给定的目标ID范围
• 菜谱关联的食材总数量，和目标ID列表的元素总数量完全相等
  两个条件同时满足时，就代表菜谱的食材集合和目标列表是完全重合的，既不会少食材，也不会多额外的食材。

通用兼容写法（适配绝大多数SQL数据库） #

-- 替换括号里的食材ID为你的目标列表即可
WITH target_ingredients AS (
    SELECT id FROM (VALUES (111), (222), (333)) AS t(id) -- 目标食材ID集合
),
recipe_ingredient_stats AS (
    SELECT 
        fk_recipe,
        COUNT(*) AS total_ingredient_count, -- 统计每个菜谱关联的全部食材总条数
        -- 判断：只要存在不在目标集合里的食材，标记为0；全部在集合里标记为1
        MAX(CASE WHEN ri.fk_ingredient NOT IN (SELECT id FROM target_ingredients) THEN 0 ELSE 1 END) AS is_all_included
    FROM Recipes_Ingredients ri
    GROUP BY fk_recipe
)
SELECT r.id, r.name
FROM Recipes r
INNER JOIN recipe_ingredient_stats rs 
    ON r.id = rs.fk_recipe
WHERE 
    -- 条件1：食材总数量和目标集合长度一致
    rs.total_ingredient_count = (SELECT COUNT(*) FROM target_ingredients)
    -- 条件2：所有食材都在目标集合范围内
    AND rs.is_all_included = 1;

这个写法可以完全规避误判：

• 如果菜谱只关联了111、222两个食材：总数量是2，比目标数3小，直接被过滤
• 如果菜谱除了三个目标食材外还加了444号食材：is_all_included会被标记为0，即使总数量达标也不会命中
• 如果菜谱关联了111、222、555三个食材：is_all_included标记为0，哪怕总数量和目标数一样，也会被过滤

注意：如果你的Recipes_Ingredients表存在同一个菜谱对应同一食材的重复记录（比如拆分多分量存储），要把COUNT(*)改成COUNT(DISTINCT fk_ingredient)，避免重复记录导致数量统计虚高。

简化写法（支持数组类型的数据库，如PostgreSQL） #

如果数据库支持数组聚合能力，可以直接把每个菜谱的食材聚合成有序数组，和目标数组做相等比对，逻辑更直观：

SELECT r.id, r.name
FROM Recipes r
INNER JOIN (
    SELECT 
        fk_recipe,
        -- 聚合时对食材ID排序，避免顺序差异导致比对失败
        ARRAY_AGG(fk_ingredient ORDER BY fk_ingredient) AS ingredient_set
    FROM Recipes_Ingredients ri
    GROUP BY fk_recipe
) ri_stats ON r.id = ri_stats.fk_recipe
-- 直接和排序后的目标食材数组比对
WHERE ri_stats.ingredient_set = ARRAY[111, 222, 333]::int[];

常见踩坑提示：

• 不要在关联Recipes_Ingredients的时候就提前过滤fk_ingredient in (目标ID列表)，这样会把菜谱的额外食材直接过滤掉，最后统计的时候就分辨不出菜谱到底是真的刚好3个食材，还是本来有4个食材被过滤掉了1个，直接导致结果不准
• 聚合食材数组的时候一定要做排序，不然哪怕元素完全一样，顺序不同的数组在数据库里也会被判定为不相等

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