SQL子集查询问题：基于食谱关联表查找给定食材ID的兼容食谱

当然可以！针对这个需求，我们可以通过几种不同的SQL思路来实现，核心是明确「兼容食谱」的定义——这里默认是指食谱所需的全部食材都包含在你提供的食材ID集合中，下面是具体的实现方案：

方法1：GROUP BY + HAVING 精准匹配全部食材 #

这种方法通过统计每个食谱的已匹配食材数，和该食谱实际需要的总食材数对比，确保所有食材都被覆盖：

-- PostgreSQL 专属写法（使用EVERY函数）
SELECT r_id
FROM recipe_ingredient
GROUP BY r_id
HAVING COUNT(DISTINCT i_id) = (
    -- 子查询获取当前食谱的总食材数量
    SELECT COUNT(DISTINCT i_id)
    FROM recipe_ingredient
    WHERE r_id = recipe_ingredient.r_id
)
AND EVERY(i_id IN (1, 3, 5)); -- 替换成你的食材ID集合

如果是MySQL、SQL Server等不支持EVERY函数的数据库，可以用CASE语句替代：

-- 跨数据库兼容写法
SELECT r_id
FROM recipe_ingredient
GROUP BY r_id
HAVING COUNT(DISTINCT i_id) = (
    SELECT COUNT(DISTINCT i_id)
    FROM recipe_ingredient ri
    WHERE ri.r_id = recipe_ingredient.r_id
)
AND SUM(CASE WHEN i_id NOT IN (1, 3, 5) THEN 1 ELSE 0 END) = 0;

逻辑说明 #

• GROUP BY r_id 将关联表按食谱ID分组，方便统计每个食谱的食材情况
• 子查询用来获取每个食谱实际需要的总食材数量
• SUM(CASE...) = 0 确保该食谱没有任何食材不在你提供的集合中，结合总数量匹配，就筛选出了所有食材都兼容的食谱

方法2：NOT EXISTS 排除法 #

这个思路更直观：直接排除那些包含非指定食材的食谱，剩下的就是完全兼容的食谱：

SELECT DISTINCT r_id
FROM recipe_ingredient ri_main
WHERE NOT EXISTS (
    SELECT 1
    FROM recipe_ingredient ri_sub
    WHERE ri_sub.r_id = ri_main.r_id
    AND ri_sub.i_id NOT IN (1, 3, 5) -- 替换成你的食材ID集合
);

逻辑说明 #

• NOT EXISTS 子查询会检查当前食谱是否存在任何一种不在指定集合里的食材
• 如果不存在这样的食材，就说明该食谱的所有食材都兼容，会被保留在结果中

特殊情况：如果「兼容」指至少使用一种指定食材 #

如果你的需求是找出至少用到了给定集合中一种食材的食谱，写法会更简单：

SELECT DISTINCT r_id
FROM recipe_ingredient
WHERE i_id IN (1, 3, 5); -- 替换成你的食材ID集合

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